Pivoting Multiple Columns More Efficiently

[Andrew415]

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

[Anonymous]

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 Pabbu

[Anonymous]

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 Pabbu

[SundarRaj · Accepted solution]

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=104...

[Andrew415]

Thank you, this works perfectly for what I am trying to accomplish.

[Andrew415]

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

[Irwan]

hello @Andrew415 

i am not sure why you want it as text but perhaps it is possible.

you can tweak the DAX however you want to match up your requirement including the blank column.

otherwise, try using @SundarRaj 's solution. it surely gives your broader insight of your case.

Thank you.

[Irwan]

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.