Forum Discussion
Power Query to shape excel to a single table
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!
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.
2 Replies
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.