Power Query to shape excel to a single table

[ttseng]

Hi,

I'm trying to use power query to extract data from an excel form. Here's an example of the source that I have:

Month	January
Cars	Qty
Blue	1
Green	2
Yellow	3
Red	4
Trucks	Qty
Orange	1
Black	2
White	1
Silver	2

I'm trying to end up with the following:

Item	Qty
Cars-Blue	1
Cars-Green	2
Cars-Yellow	3
Cars-Red	4
Trucks-Orange	1
Truck-black	2
Truck-white	1
Truck-silver	2

I'm able to do it for cars using the method outlined here but cannot seem to figure out how to add trucks into the single query. I'm also able to do it in 2 seperate queries and then append but would like to know how best to combine into a single query. If anyone can help that would be great or point me to any resources I'll be eternally grateful!

I've uploaded an excel of the sample dataset for your convenience [click to download]

Thanks!

[Anonymous · Accepted solution]

Hey,

I was able to solve it using the code below.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "Month")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Column2] = null then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Column2] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Custom", "Item"}, {"Column1", "Color"}, {"Column2", "Qty"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Color", "Item", "Qty"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Qty", type number}, {"Item", type text}})
in
    #"Changed Type1"

The result I get from it is the following

I hope it helps.

[ttseng]

thank you @Anonymous !

After looking at your solution I can only facepalm at it's simplicity. Thank you!