Search code examples
powerquerym

Arranging Data in a Query Table

I'm trying to figure out a simple way (using PowerQuery) to convert this:

enter image description here

into this:

![enter image description here

I've spent days trying to figure out a simple way to do it.

Everything I've tried has failed.

Solution

  • You can use Group By on the Transform tab, group by project and define a aggregation for each of the segment columns (e.g. Sum). Then adjust the created code from List.Sum to List.RemoveNulls. Then add a column with nested tables from the segment columns, using Table.FromColumns. Remove the original segment columns and expand the nested tables.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Segment1", type text}, {"Segment2", type text}, {"Segment3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Segment1", each List.RemoveNulls([Segment1]), type text}, {"Segment2", each List.RemoveNulls([Segment2]), type text}, {"Segment3", each List.RemoveNulls([Segment3]), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Tabled", each Table.FromColumns({[Segment1],[Segment2],[Segment3]},{"Segment1","Segment2","Segment3"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Segment1", "Segment2", "Segment3"}),
    #"Expanded Tabled" = Table.ExpandTableColumn(#"Removed Columns", "Tabled", {"Segment1", "Segment2", "Segment3"}, {"Segment1", "Segment2", "Segment3"})
in
    #"Expanded Tabled"