Forum Discussion
Advocate IIPivot/Unpivot/Transpose? which one to get the expected table format
Hi All
I've got this tale:
| Jan/23 | Jan/23 | Jan/23 | Feb/23 | Feb/23 | Feb/23 | Mar/23 | Mar/23 | Mar/23 | |
| Account No. | Realized | Budget | Difference | Realized | Budget | Difference | Realized | Budget | Difference |
| 100 | 10000 | 2000 | 8000 | 10000 | 2000 | 8000 | 10000 | 2000 | 8000 |
| 200 | 21000 | 4800 | 16200 | 23000 | 4200 | 18800 | 25000 | 5600 | 19400 |
| 300 | 44100 | 11520 | 32580 | 48300 | 10080 | 38220 | 52500 | 13440 | 39060 |
| 400 | 92610 | 27648 | 64962 | 101430 | 24192 | 77238 | 110250 | 32256 | 77994 |
And i'd like to change it to the following format:
| Account No. | Realized | Budget | Difference | Date |
| 100 | 10000 | 2000 | 8000 | Jan/23 |
| 200 | 21000 | 4800 | 16200 | Jan/23 |
| 300 | 44100 | 11520 | 32580 | Jan/23 |
| 400 | 92610 | 27648 | 64962 | Jan/23 |
| 100 | 10000 | 2000 | 8000 | Feb/23 |
| 200 | 23000 | 4200 | 18800 | Feb/23 |
| 300 | 48300 | 10080 | 38220 | Feb/23 |
| 400 | 101430 | 24192 | 77238 | Feb/23 |
| 100 | 10000 | 2000 | 8000 | Mar/23 |
| 200 | 25000 | 5600 | 19400 | Mar/23 |
| 300 | 52500 | 13440 | 39060 | Mar/23 |
| 400 | 110250 | 32256 | 77994 | Mar/23 |
Any great tips and tricks to get this table format in PowerQuery nice and easy?
The issue with your source is it has multiple header rows... it makes unpivoting more complicated. I found this post which is a situation similar to yours, give this a try? https://community.fabric.microsoft.com/t5/Desktop/Unpivot-multiple-headers-in-a-data/m-p/1895522#M726946
2 Replies
- christinepayton
Most Valuable Professional
The issue with your source is it has multiple header rows... it makes unpivoting more complicated. I found this post which is a situation similar to yours, give this a try? https://community.fabric.microsoft.com/t5/Desktop/Unpivot-multiple-headers-in-a-data/m-p/1895522#M726946
Hi MacJasem ,
Please have a try.
Use the unpivot columns operation, which allows you to convert multiple columns into attribute-value pairs. To use this option, you will need to:
Select the Account No. column and right-click on it. Choose Unpivot Other Columns from the menu.
Rename the Attribute column as Date and the Value column as Realized.
Select the Date column and right-click on it. Choose Split Column > By Number of Characters from the menu.
In the dialog box, enter 6 as the number of characters to split by and select At the Right as the split option. Click OK.
Rename the new column as Budget and change its data type to Whole Number.
Select the Realized and Budget columns and right-click on them. Choose Add Column > Standard > Subtract from the menu.
Rename the new column as Difference.Table.TransformColumns - PowerQuery M | Microsoft Learn
Transpose table - Power Query | Microsoft Learn
How to transform table in Power Query - Microsoft Fabric Community
Table.TransformRows - PowerQuery M | Microsoft Learn
How to Get Your Question Answered Quickly
If it does not help, please provide more details.
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
