Applying a function while grouping (or on a group)

[salihhh]

I have a situation where while grouping (or post the grouping is also fine) I want to select only a particular value from the last group based on a condition.

Suppose I have the below table. I write a function that prioritze which SubProduct to choose while grouping. In this case CC>BB>AA is the priority order, when they appear in the same group.

The resulting table will look like this. Row count is not important, as its just for the sake of grouping. 

Can we accomplish this using power query ? 

[wdx223_Daniel · Accepted solution]

NewStep=Table.Group(PreviousStepName,"Product",{{"SubProduct",each List.Sort([SubProduct],each List.PositionOf({"CC","BB","AA"},_)){0}},{"Row Count",Table.RowCount}})

[dufoq3 · Accepted solution]

Hi @salihhh, similar approach:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCtA1MjRW0lFydFSK1YFwTTC5Tk5wrhkm19lZKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, SubProduct = _t]),
    GroupedRows = Table.Group(Source, {"Product"}, {{"SubProduct", each Table.Last(_)[SubProduct], type text}, {"Row Count", each Table.RowCount(_), Int64.Type}})
in
    GroupedRows

[wdx223_Daniel · Accepted solution]

NewStep=Table.Group(PreviousStepName,"Product",{{"SubProduct",each List.Sort([SubProduct],each List.PositionOf({"CC","BB","AA"},_)){0}},{"Row Count",Table.RowCount}})