Max Group Filter Power Query

Question

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)

@David just added examples tables, will this help out? Thank you in advance! — chaosKnight, Aug 29 '22 at 11:04
I just updated the first row since it was not the value I wanted! The goal is to get the max tax, and aftwards the max code for that given tax! — chaosKnight, Aug 29 '22 at 11:58

score 1 · Accepted Answer · answered Aug 29 '22 at 12:30

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"

Yes David! Thank you so much for your help! Would you be able to give me hints on what each transformation step actually does? What does the step group rows with ID and ALL actually needs to be done?Before the Added Custom step? Thank you once again! — chaosKnight, Sep 01 '22 at 13:31
#"Grouped Rows": merely groups by id but puts all rows in subtables. #"Added Custom": sorts the sub tables by TAX and also CODE and then extracts the first row. The next lines merely remove the redundant columns and expand the record. You can step through the code in the applied steps pane. — Davide Bacci, Sep 01 '22 at 13:45

score 0 · Answer 2 · answered Aug 29 '22 at 11:08

0

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"}, {{"TAX", each List.Max([TAX]), type nullable number}, {"CODE", each List.Max([CODE]), type nullable number}})
in
    #"Grouped Rows"

answered Aug 29 '22 at 11:08

Davide Bacci

16,647
3
10
36

I'm sorry David the output desired was incorret for the first row, altho in the second was right! Can you please consider the new updated table output? – chaosKnight Aug 29 '22 at 11:42
I'll add a new answer. – Davide Bacci Aug 29 '22 at 12:19
Is your question answered? – Davide Bacci Aug 30 '22 at 07:47

Max Group Filter Power Query

2 Answers2