Power Query Custom Column Formula

I am trying to aggregate data brought in via power query and add a custom column. Essentially, I want to count all the first non-zero months in the data feed by group and report that count in a separate column.

For an example, this is what the input data looks like:

Index	UNIQUEID	PRMS Reserves Category	PRMS Reserves Sub Category	Date	Gross Oil Well Head Volume (MBBL)
36	INPTegrxW4xbF6	probable	undeveloped	6/1/2027	0
36	INPTegrxW4xbF6	probable	undeveloped	7/1/2027	0
36	INPTegrxW4xbF6	probable	undeveloped	8/1/2027	0
36	INPTegrxW4xbF6	probable	undeveloped	9/1/2027	14
36	INPTegrxW4xbF6	probable	undeveloped	10/1/2027	24
36	INPTegrxW4xbF6	probable	undeveloped	11/1/2027	27
37	INPTegrxW4xbF7	probable	undeveloped	6/1/2027	0
37	INPTegrxW4xbF7	probable	undeveloped	7/1/2027	0
37	INPTegrxW4xbF7	probable	undeveloped	8/1/2027	0
37	INPTegrxW4xbF7	probable	undeveloped	9/1/2027	14
37	INPTegrxW4xbF7	probable	undeveloped	10/1/2027	24
37	INPTegrxW4xbF7	probable	undeveloped	11/1/2027	27
38	INPTegrxW4xbF8	probable	undeveloped	6/1/2027	0
38	INPTegrxW4xbF8	probable	undeveloped	7/1/2027	0
38	INPTegrxW4xbF8	probable	undeveloped	8/1/2027	0
38	INPTegrxW4xbF8	probable	undeveloped	9/1/2027	14
38	INPTegrxW4xbF8	probable	undeveloped	10/1/2027	24
38	INPTegrxW4xbF8	probable	undeveloped	11/1/2027	27
39	INPTegrxW4xbF9	proved	undeveloped	5/1/2027	0
39	INPTegrxW4xbF9	proved	undeveloped	6/1/2027	0
39	INPTegrxW4xbF9	proved	undeveloped	7/1/2027	0
39	INPTegrxW4xbF9	proved	undeveloped	8/1/2027	14
39	INPTegrxW4xbF9	proved	undeveloped	9/1/2027	24
39	INPTegrxW4xbF9	proved	undeveloped	10/1/2027	27

and I would like the final query to look like this:

PRMS Reserves Category	PRMS Reserves Sub Category	Date	Sum of Gross Oil	Count
probable	undeveloped	6/1/2027	0	0
probable	undeveloped	7/1/2027	0	0
probable	undeveloped	8/1/2027	0	0
probable	undeveloped	9/1/2027	42	3
probable	undeveloped	10/1/2027	72	0
probable	undeveloped	11/1/2027	81	0
proved	undeveloped	5/1/2027	0	0
proved	undeveloped	6/1/2027	0	0
proved	undeveloped	7/1/2027	0	0
proved	undeveloped	8/1/2027	14	1
proved	undeveloped	9/1/2027	24	0
proved	undeveloped	10/1/2027	27	0

Does anyone have a suggestion on how to write the function in power query's custom column?

Solution

It looks like you need to calculate the minimal date MinDate for each category combination and then check if Date = MinDate

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZGxCoMwFEX/JbNgklqT/EChS+lQ6CAODYYuUkWo+PlN62DNe8vV7SVwznBPVYlDKTJxvlxv4TlM92Lyp+9HP3T+4dsQz/erCWNouz408VXmKtdSm3hKUWcwb3bydifvFl4VWwRKLga9zaD+DGY2mNRgsAgYTyNgPI2A8UwETMBFAA1MBJsaLBYB42kEjKcRMJ6JgAm4CKCBieBSg5sN449Y80cyAULTgAhN8yG0peMjuKPLI/iqXNy9/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, UNIQUEID = _t, #"PRMS Reserves Category" = _t, #"PRMS Reserves Sub Category" = _t, Date = _t, #"Gross Oil Well Head Volume (MBBL)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"UNIQUEID", type text}, {"PRMS Reserves Category", type text}, {"PRMS Reserves Sub Category", type text}, {"Date", type date}, {"Gross Oil Well Head Volume (MBBL)", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Gross Oil Well Head Volume (MBBL)"] <> 0)),
    #"Grouped Category" = Table.Group(#"Filtered Rows", {"PRMS Reserves Category", "PRMS Reserves Sub Category"}, {{"MinDate", each List.Min([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PRMS Reserves Category", "PRMS Reserves Sub Category"}, #"Grouped Category", {"PRMS Reserves Category", "PRMS Reserves Sub Category"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MinDate"}, {"MinDate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Date] = [MinDate] then 1 else 0, Int64.Type),
    #"Grouped Category and Date" = Table.Group(#"Added Custom", {"PRMS Reserves Category", "PRMS Reserves Sub Category", "Date"}, {{"Gross", each List.Sum([#"Gross Oil Well Head Volume (MBBL)"]), type nullable number}, {"Count", each List.Sum([Custom]), type number}})
in
    #"Grouped Category and Date"

This computes the MinDate in the #"Grouped Category" step by taking the min over the Date column after filtering out 0 values:

Then it merges this MinDate column back into the original table and adds a custom column using the logic if [Date] = [MinDate] then 1 else 0.

The final step groups on the first three columns shown above, summing over Gross Oil and Custom.