PDA

View Full Version : Expanding expanding data...!



Martinl
2016-12-09, 02:18 PM
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
2016-12-09, 04:20 PM
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
2016-12-12, 10:53 AM
[QUOTE=Comfy;29065]
I would Import the File.
Use "Unpivot Other Columns"[QUOTE]

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