Forum Discussion
Converting columns to rows
Hi all,
I am trying to covert a file which is similar to the below
to the below format
Thanks, Kumar
3 Replies
Use the Transpose feature: https://support.office.com/en-us/article/Transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419F2E3-BEAB-4318-AAE5-D0F862209744
Alan, I don't think transpose works here. With it you'll have 4 regions columns with month/sales rows.
Actually we need here to combine all months and sales columns into one pair of columns with months and sales.
Hi Kumar,
With Power Query it could be like
- query your source table
- select region and unpivot other columns
- add two index columns, one starts from 0 and another from 1
- merge table with itself on above two columns
- expand received table column with Value.1 Thus you have two columns with values - in one row if in one is month in another one is sales, and opposite
- filter attribute on only months (or only sales)
- sort months ascending and do cosmetic (remove other columns, rename the rest, etc)
