Expanding expanding data...!

[Martinl]

I get an awful monthly file like this called Monthly Sales of Widgets.xlsx which I need to work with.

Month 1

Product ID	Jan-16
Widget1	122
Widget2	2444

Month 2

Product ID	Jan-16	Feb-16
Widget1	122	102
Widget2	2444	2001

Month 3 etc

Product ID	Jan-16	Feb-16	Mar-16
Widget1	122	102	98
Widget2	2444	2001	3021

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.

 

[Comfy]

Can you post your M?

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

Table1

Product ID	Jan-16	Feb-16
Widget1	122	102
Widget2	2444	2001

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.

 

[Martinl2]

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

Awesome, now I feel so dim...:thumb: