Search code examples
maxpowerquerygroupcustomcolumn

Max Group Filter Power Query

I'm trying to use the following code to get a new added column with information about a max value from a given field.

But what I would want is to have the possiblity to group a table where I filter it by a condition where the Custom column value would be 1.

I need to transform somehow the following parte of the code:

 [
                filter = [ID] /*should I add here another filter?/,
                max2=Table.Group(
                    Source, {"ID"},
                    {{"MaxFiltered2", each List.Max([CODE])}}
                ){[ID=filter]}[MaxFiltered2]
            ][max2]
    )

Here follows the code sequence:

    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "maxInt",
        each
            [
                filter = [ID],
                max=Table.Group(
                    Source, {"ID"},
                    {{"MaxFiltered", each List.Max([TAX])}}
                ){[ID=filter]}[MaxFiltered]
            ][max]
    ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [maxInt]=[TAX] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
    #"Added Custom2" = Table.AddColumn(
    #"Changed Type",
        "maxInt2",
        each
            [
                filter = [ID],
                max2=Table.Group(
                    Source, {"ID"},
                    {{"MaxFiltered2", each List.Max([CODE])}}
                ){[ID=filter]}[MaxFiltered2]
            ][max2]
    )
in
    #"Added Custom2"

Inputput desired:

ID  TAX CODE
A   4   921
A   6   500
A   6   200
B   2   700
B   2   500
B   1,5 100

Output desired:

ID  TAX CODE
A   6   500
B   2   700

(to get the max for A and B IDs both on TAX and CODE variables)

Solution

  • enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIBYksjQ6VYHQjfDIhNDQxQ+EZQvhOIDcTmaHxTJL6hnimIBInEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TAX = _t, CODE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"TAX", type number}, {"CODE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All", each _, type table [ID=nullable text, TAX=nullable number, CODE=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", 
        each Table.First(
            Table.Sort([All],{{"TAX", Order.Descending},{"CODE", Order.Descending}})
        )
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "All"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ID", "TAX", "CODE"}, {"ID", "TAX", "CODE"})
in
    #"Expanded Custom"