M language - Add custom column using filter in Power BI

[tod]

Hi,

I'm looking for an M language code I can use in PBI Power Query to add a custom column that looks up the max value in one column based on a filter in another.  Below is an example of the data set (in reality there are millions of records).  The third column to the right is the desired outcome of the added custom column. The logic here is that the added column is looking for the largest value from the first column from Category A in the second column. Since there is no category A in 202104 and 202105 in the first column, the largest value in the new custom column is 202103. All data has a text attribute.

(text)	(text)	(text)
YYYYMM	Category	M_Column
202012	A	202103
202012	B	202103
202012	C	202103
202012	D	202103
202101	A	202103
202101	B	202103
202101	C	202103
202101	D	202103
202102	A	202103
202102	B	202103
202102	C	202103
202102	D	202103
202103	A	202103
202103	B	202103
202103	C	202103
202103	D	202103
202104	B	202103
202104	C	202103
202104	D	202103
202105	B	202103
202105	C	202103
202105	D	202103

Any and all help appreciated.

Thanks

[FrankAT]

Hi @tod ,

I think you can do it like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc27DQAhDATRXhwT+IMLOLguEP23cQFC8lw40j7tWuLqai5NHtmt5GBO5nvT1Ko9OZiTWazTOq3T/n6DNmiDNmg7x53jznFynBznGe8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YYYYMM = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YYYYMM", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Category"}, #"Table (2)", {"Category"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"YYYYMM"}, {"Outcome"})
in
    #"Expanded Table (2)"

// Table (2)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc27DQAhDATRXhwT+IMLOLguEP23cQFC8lw40j7tWuLqai5NHtmt5GBO5nvT1Ko9OZiTWazTOq3T/n6DNmiDNmg7x53jznFynBznGe8P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YYYYMM = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YYYYMM", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"Count", each _, type table [YYYYMM=nullable number, Category=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Sorted", each Table.Sort([Count],{{"YYYYMM", Order.Ascending}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.Last([Sorted])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"YYYYMM"}, {"YYYYMM"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"YYYYMM", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count", "Sorted"})
in
    #"Removed Columns"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Fowmy · Accepted solution]

@tod 

Are you trying to get the largest value from column 1 where the category is "A" in Column 2?
Add the following column.

List.Max( Table.SelectRows(#"Changed Type", each [Category]="A")[YYYYMM] )

[tod]

This works!! Great and thank you.

[lance_6]

Is there a reason you don't want to use DAX to add your calculated column?