Aug 19 2021 06:22 AM
Hi all,
I have a dataset with the first column header of 'SKU List' with 3000 SKUs listed. The next 10 columns headers are sales from 2010 to 2020 and the contents in the cells are the number of units sold. Hence, right now, there are a total of 11 columns.
If I would like to move the 2010 to 2020 columns to a single 'Year' Column with the units sold in the next column (compressing my dataset contents to 3 columns), what is the best way? Doing this so I can add further variables in the future instead of using the year numbers as a column header. I do understand that it will make my Excel dataset a lot longer (as each SKU goes from 1 row to 10 rows) but that's fine.
Thanks for your help in advance.
Aug 19 2021 06:37 AM
@pheerful Connect to the entire table with PowerQuery. Select the first column. Right-click and select Unpivot other columns. Close and load back to a table.
Aug 19 2021 08:57 AM
Aug 19 2021 09:18 AM
@pheerful From Excel 2016, PQ is integrated in Excel. So you are on an older version?
Aug 19 2021 09:39 AM
Aug 19 2021 09:51 AM
@pheerful Okay, then you may forget it!
Aug 19 2021 01:20 PM
If from left (Source) to right (Target)
- create Target table with headers and only one empty row. Add formulas to it
Year:
=INDEX(Source[SKU], MOD(ROW()-ROW(Target[[#Headers],[SKU]])-1,ROWS(Source))+1)
SKU:
=INDEX(Source[#Headers],1,INT((ROW()-ROW(Target[[#Headers],[SKU]])-1)/ ROWS(Source) )+2)
Qty:
=INDEX(Source, MATCH([@SKU],Source[SKU],0), MATCH([@Year],Source[#Headers],0))
-starting from this first row select the range where to expand, e.g. type in name box P5:R40000 if P5 is the reference on left top cell with data in Target table
- Ctrl+D
- Target table will be expanded on entire range.
I did that on Windows, on Mac it shall be similar.