Forum Discussion

tod's avatar
tod
Frequent Visitor
5 years ago
Solved

M language - Add custom column using filter in Power BI

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 se...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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] )

     

     









FrankAT's avatar
FrankAT
Community Champion
5 years ago

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)