Expanding expanding data...!

Martinl

New member
Joined
Dec 2, 2016
Messages
15
Reaction score
0
Points
0
Excel Version(s)
2016 MSO (16.0.9126.2259)
I get an awful monthly file like this called Monthly Sales of Widgets.xlsx which I need to work with.

Month 1
Product IDJan-16
Widget1122
Widget22444

Month 2
Product IDJan-16Feb-16
Widget1122102
Widget224442001

Month 3 etc
Product IDJan-16Feb-16Mar-16
Widget112210298
Widget2244420013021

How do you get Power Query to import this file with the additional columns each month without having to go in and manually adjusting the columns expanded.
 
Can you post your M?

I would Import the File.
Use "Unpivot Other Columns"

Table1

Product IDJan-16Feb-16
Widget1122102
Widget224442001

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Product ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns"

As long as the table contains a column called [Product ID] it will run and include any additional columns.
 
Back
Top