Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.

Reply
KamilGH
Frequent Visitor

Turning Only Some Rows into Columns

Hi everyone,

 

I'm looking for a way to resort the data in Power Query as the following:

Original data example:

KamilGH_0-1675370772900.png

 

Intended result:

KamilGH_1-1675370787513.png

 

Any idea would be appreciated.

 

Thanks,

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1675377780164.png

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1675377780164.png

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors