Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ?
Solved! Go to Solution.
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
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
NewStep=Table.Group(PreviousStepName,"Product",{{"SubProduct",each List.Sort([SubProduct],each List.PositionOf({"CC","BB","AA"},_)){0}},{"Row Count",Table.RowCount}})