Turning Only Some Rows into Columns

[KamilGH]

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,

[AlexisOlson · Accepted 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: