Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm looking for a way to resort the data in Power Query as the following:
Original data example:
Intended result:
Any idea would be appreciated.
Thanks,
Solved! Go to Solution.
This is possible but the resulting table will be more difficult to work with. I'd recommend keeping the data unpivoted in your data model (you can always pivot it in a report visual). If you need a table with unique rows per family_id, then I'd recommend creating a related family dimension table.
If you really need to do this for some reason, here's one possible method:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjNU0gESuskZmTkpCqgchcT0VKVYHXRFRigchCKwuBGySUaYJqEoQuXgUGSMwoEoigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [family_id = _t, #"child #" = _t, #"child age" = _t]),
#"Grouped Rows" = Table.Group(Source, {"family_id"}, {{"Count", Table.RowCount, Int64.Type}, {"Collapse Rows", each Text.Combine(List.Union(List.Zip({[#"child #"], [child age]})), ", "), type text}}),
maxCount = List.Max(#"Grouped Rows"[Count]),
ColNames = List.Union(List.Transform({1..maxCount}, each {"child #" & Number.ToText(_), "child age " & Number.ToText(_)})),
#"Expand to Columns" = Table.SplitColumn(#"Grouped Rows", "Collapse Rows", Splitter.SplitTextByDelimiter(", "), ColNames)
in
#"Expand to Columns"
Result:
This is possible but the resulting table will be more difficult to work with. I'd recommend keeping the data unpivoted in your data model (you can always pivot it in a report visual). If you need a table with unique rows per family_id, then I'd recommend creating a related family dimension table.
If you really need to do this for some reason, here's one possible method:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjNU0gESuskZmTkpCqgchcT0VKVYHXRFRigchCKwuBGySUaYJqEoQuXgUGSMwoEoigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [family_id = _t, #"child #" = _t, #"child age" = _t]),
#"Grouped Rows" = Table.Group(Source, {"family_id"}, {{"Count", Table.RowCount, Int64.Type}, {"Collapse Rows", each Text.Combine(List.Union(List.Zip({[#"child #"], [child age]})), ", "), type text}}),
maxCount = List.Max(#"Grouped Rows"[Count]),
ColNames = List.Union(List.Transform({1..maxCount}, each {"child #" & Number.ToText(_), "child age " & Number.ToText(_)})),
#"Expand to Columns" = Table.SplitColumn(#"Grouped Rows", "Collapse Rows", Splitter.SplitTextByDelimiter(", "), ColNames)
in
#"Expand to Columns"
Result:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |