Find MAX Value within a column

[powerlight1]

Hi,

I am new to powerquery and can only do very basic things however I have a situation where I want to create a new column that has the MAX or MIN of another column within the same table.

In the below table, I have duplicates for code 380270 and I want to create/add a new column that is the MIN/MAX of the SellingClass column grouped by the first 5 columns:

Thanks

[MarkLaf]

This can be done through the UI. I showed the steps by clicking through the ribbons, but you can alternatively perform same actions through right-click menu.

1) Select the first four columns (I just used defaults since you hid names). Then go to Transform > Group By

2) In the Group By window, make selections for the following aggregations

3) Select your three new agg columns and then Remove Other Columns (ie select your new columns)

4 ) Click the '↰↱' button on the ALL ROWS column, uncheck 'Use original column name as prefix', and click okay. This brings back all your original columns/rows

Output:

[KNP · Accepted solution]

Hi @powerlight1,

Create a new blank query. Open the advanced editor and paste in this code. You should be able to see what is happening in each step.

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMrYwMDI3UNJRSkvMSykD0UWlmSXFQEZqGpAw0k1LLC5RitUhqNJYNzc1JbM0Vyk2FgA=", BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [id = _t, category = _t, #"type" = _t, subtype = _t, sellingclass = _t]
    ),
    ct = Table.TransformColumnTypes(
        Source,
        {
            {"id", Int64.Type},
            {"category", type text},
            {"type", type text},
            {"subtype", type text},
            {"sellingclass", type text}
        }
    ),
    group = Table.Group(
        ct,
        {"id", "category", "type", "subtype"},
        {
            {"minsc", each List.Min([sellingclass]), type nullable text},
            {"maxsc", each List.Max([sellingclass]), type nullable text}
        }
    ),
    merge = Table.NestedJoin(
        ct,
        {"id", "category", "type", "subtype"},
        group,
        {"id", "category", "type", "subtype"},
        "group",
        JoinKind.LeftOuter
    ),
    expandrows = Table.ExpandTableColumn(merge, "group", {"minsc", "maxsc"}, {"minsc", "maxsc"})
in
    expandrows

The group step groups everything together and grabs the min and max values. You then merge with the CT step and the group step and expand the min-max columns. Power Query allows you to reference any step; it doesn't have to be the previous one.

Let me know if you have any questions.