Flatten Table with Power Query

[ilya2004]

Hello,

I have a table whose data looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesman Name[/TD]
[TD]Tables[/TD]
[TD]Ladders[/TD]
[TD]Chairs[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

I would like to flatten it so that the table reads like the below and remove the repetition of the key column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Salesman Name[/TD]
[TD]Tables[/TD]
[TD]Ladders[/TD]
[TD]Chairs[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[ilya2004]

I should add that there are multiple columns to the left of the data which is being flattened that are redundant, but I can flatten by the uniqueID in the first column.

 

[gazpage]

Unpivot those three columns, filter out the blanks, and then repivot?

 

[ilya2004]

That doesn't work because the blanks were created as the result of a pivot, when I unpivot it, the blanks go away on its own, but when you pivot, they come back.

 

[gazpage]

Huh, I thought that would work.

Here is what I did:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTIFYhCK1YELAZGhAaYYEFkqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sales Person" = _t, Tables = _t, Ladders = _t, Chairs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Person", type text}, {"Tables", Int64.Type}, {"Ladders", Int64.Type}, {"Chairs", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Sales Person"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Returns the result I expected. Didn't even need to remove blanks.

 

[ilya2004]

For me, it just produces a flat column that looks like this:

John Tables 5
John Ladders 10
John Chairs 9

Which is how the data started out to begin with.

My goal is to have Tables, Ladders and chairs as columns and that each unique ID should not get more than one row on the table.

 

[gazpage]

Really strange. You copied my code directly into a new query? Do we have different versions of Power BI? How can we get a different result from the same query?

 

[gazpage]

For me, it just produces a flat column that looks like this:

John Tables 5
John Ladders 10
John Chairs 9

Actually, if you can get this far, why can't you just pivot the data by the middle column, using the third column as the values and select don't aggregate under advanced?

 

[citizenbh]

My solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Salesman Name"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

 

[MarcelBeug]

Alternatively you can Group By Salesman, sum the values and take care of flattening the columns to te left you mentioned in post number 2.

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Salesman Name"}, {{"Tables", each List.Sum([Tables]), type number}, {"Ladders", each List.Sum([Ladders]), type number}, {"Chairs", each List.Sum([Chairs]), type number}})
in
    #"Grouped Rows"