Forum Discussion
Applying a function while grouping (or on a group)
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 ?
NewStep=Table.Group(PreviousStepName,"Product",{{"SubProduct",each List.Sort([SubProduct],each List.PositionOf({"CC","BB","AA"},_)){0}},{"Row Count",Table.RowCount}})
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
2 Replies
NewStep=Table.Group(PreviousStepName,"Product",{{"SubProduct",each List.Sort([SubProduct],each List.PositionOf({"CC","BB","AA"},_)){0}},{"Row Count",Table.RowCount}})
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