Forum Discussion
Pivoting Multiple Columns More Efficiently
Hi, I am trying to find a more efficient way to multiple pivot columns in Power Query from a shipping report. I'm currently selecting each pair of columns (description and adjacent amount), pivoting the columns, and then removing the empty column from the pivoted pair.
A couple of my headaches with the raw data is
1) headers for the "tracking ID charge descriptions" and amounts are not static ("charge description_3" might say remote residential one week, and then fuel surcharge another week)
2) there are ~100 columns of "charge description" and "charge amount" that need to be paired together, and most will be completely null/empty
Data sample
| Tracking ID | Date | Tracking ID Charge Description | Tracking ID Charge Amount | Tracking ID Charge Description_1 | Tracking ID Charge Amount_2 | Tracking ID Charge Description_3 | Tracking ID Charge Amount_4 | Tracking ID Charge Description_104 | Tracking ID Charge Amount_105 | Tracking ID Charge Description_106 | Tracking ID Charge Amount_107 |
| 123 | 1/1/2025 | Fuel Surcharge | 1.25 | Remote Residential | 15 | ||||||
| 234 | 1/7/2025 | Fuel Surcharge | 3.4 | Address Correction | 13.5 | ||||||
| 345 | 1/21/2025 | Extended Residential | 7 | Fuel Surcharge | 2.1 | Address Correction | 13.5 | ||||
| 567 | 1/28/2025 | Fuel Surcharge | 4.5 | Address Correction | 13.5 | ||||||
| 456 | 2/6/2025 | Fuel Surcharge | 8.2 | Remote Residential | 15 | Address Correction | 13.5 | ||||
| 678 | 2/12/2025 | Fuel Surcharge | 2.4 | ||||||||
| 890 | 2/19/2025 | Fuel Surcharge | 1.3 | ||||||||
| 901 | 2/27/2025 | Extended Residential | 7 | Fuel Surcharge | 6.5 |
End-state
| Tracking ID | Date | Extended Residential | Fuel Surcharge | Remote Residential | Address Correction |
| 123 | 1/1/2025 | 1.25 | 15 | ||
| 234 | 1/7/2025 | 3.4 | 13.5 | ||
| 345 | 1/21/2025 | 7 | 2.1 | 13.5 | |
| 567 | 1/28/2025 | 4.5 | 13.5 | ||
| 456 | 2/6/2025 | 8.2 | 15 | 13.5 | |
| 678 | 2/12/2025 | 2.4 | |||
| 890 | 2/19/2025 | 1.3 | |||
| 901 | 2/27/2025 | 7 | 6.5 |
Hi Andrew415 ,
With your source data, I came up with a solution in Power Query. Do have a look. Thanks!
https://docs.google.com/spreadsheets/d/1EIpZo5-DOpKhDYU4IMOfQYYtWdMh6sx7/export?format=xlsx&ouid=104752674875039603034&rtpof=true&sd=true
7 Replies
hello Andrew415
i dont know how many column you want to show in your visual.
if your visual only contain couple columns such as Extended Residential, Fuel Surcharge, Remote Residential, and Address Correction, maybe measure should be sufficient for those four instead of unpivoting ~100 columns.
Hope this will help.
Thank you.
Would it be possible to list the measure here as text? I'll give it a go and see if it might help. The blank columns wouldn't need to be part of the visual
Hi Andrew415 ,
With your source data, I came up with a solution in Power Query. Do have a look. Thanks!
https://docs.google.com/spreadsheets/d/1EIpZo5-DOpKhDYU4IMOfQYYtWdMh6sx7/export?format=xlsx&ouid=104752674875039603034&rtpof=true&sd=trueThank you, this works perfectly for what I am trying to accomplish.
Hi Andrew415,
As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay PabbuHi @Andrew415,
As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu
