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 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)
YYYYMMCategoryM_Column
202012A202103
202012B202103
202012C202103
202012D202103
202101A202103
202101B202103
202101C202103
202101D202103
202102A202103
202102B202103
202102C202103
202102D202103
202103A202103
202103B202103
202103C202103
202103D202103
202104B202103
202104C202103
202104D202103
202105B202103
202105C202103
202105D202103

 

Any and all help appreciated.

Thanks

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

     

     









4 Replies

  • lance_6's avatar
    lance_6
    Helper II
    5 years ago

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

  • Fowmy's avatar
    Fowmy
    Super User
    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] )

     

     









    • tod's avatar
      tod
      Frequent Visitor
      5 years ago

      This works!! Great and thank you.

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