Unpivotting multiple "dual columns"


Below is a screenshot showing what I need to achieve. It is a table representing the number of overtime (OT) worked by an employee, as well as the associated pay for this overtime. There are 3 different types of overtime. I need to get my data into the format as shown. (pbix attached at the bottom)

Could anyone please help me? I have tried to work through various explanations from googled solutions but I’m struggling.

As always, thank you.
Q.05 Unpivot Multiple Dual columns.pbix (53.7 KB)

Hi @michellepace,

When you have a power query design question please always include the data, XLSX or CSV is fine. Thanks!

Does this works?
Michelle.pbix (33.3 KB) Michelle.xlsx (42.0 KB)

Hello @AntrikshSharma. Thank you for your reply. I also tried your solution but it does not work. I check it against a single row of data. The dollar total and hour total for a particular row as calucated by your solution is incorrect. I’ve attached a pbix file to illustrate what I mean.

Anyone else know how I can “unpivot” multiple “dual columns”? That is, in the attached excel I’d like to replace all the green columns by 3 columns. Namely “Rate Type”, “Hours” and “Dollar_cost”?

Employee_Worked_Hours.xlsx (54.0 KB)
Q.05 Unpivot Multiple Dual columns - RETRY.pbix (33.2 KB)


As always, thank you.

see if this will work (I don’t have time to write it up now, but will check back after dinner to see if this was your solution.)

I had to start with a fresh sample, so I just grabbed a handful of folks that were in your Excel file that had OT hours for the first tier. SOrry about that.Test Solution.pbix (20.1 KB)

Hi @michellepace,

If you change the FileLocation parameter the queries will be restored.
Extracted the values in 3 separate lists

Extracted the values

Split by Delimeter and Renamed Columns

With this result

Here’s the sample file. eDNA - Unpivot Multiple Dual columns.pbix (51.0 KB)

I hope this is helpful.

1 Like

Hi @michellepace

One more solution for your requirement.

Unpivotting multiple columns.pbix (100.6 KB)

1 Like

okay - back from dinner with hubby - now on to explanation of solution (and an updated file)
Original file in my response above was a select group of rows from your table, new updated file uses your original version, and is linked to the FullTable from your sample (as well as the same single row table you were testing with).

although it looks like you have some great solutions already, I didn’t want to leave this uncomplete

  1. add an index column (great for debugging down the line so you can prove that everything unpivoted properly.
  2. unpivot Hours columns
    2a. I handled the column rename in step one, if you do not want to do that - rename columns
  3. unpivot Dollars columns
    3a. again, rename handled in the unpivot step, if you do not do this - rename columns to keep track
  4. clean up the descriptor for the column with the Dollars_OT@ values (you need to match this against the OT @ columns)
  5. add conditional column comparing the two descriptor columns, use this to either return the Dollar values or a null value
  6. filter nulls out of column in step 5, and cleanup unneeded columns


Updated Test Solution.pbix (70.3 KB)


Hi @Melissa, @Heather and @Rajesh,

Thank you all for your solutions! I can quote honestly say I would have NEVER arrived at any. For anyone other beginner this may help - - I have combined all 3 solutions into one file. I’ve made a comment on each of your solutions (for myself - I’ll refer back to this in the future).

Q.05 Unpivot Multiple Dual columns - Solutions x3.pbix (53.4 KB)
Employee_Worked_Hours.xlsx (54.0 KB)

@Melissa, for the custom column you created - can you please tell me what you clicked on to do this? Or did you write the code yourself?

Thank you all once again


Hi @michellepace,

You’ve guessed it. There’s no button for that, I wrote it for the most part in the Add Custom Column window then copy/paste large part of the syntax making small changes to extract each list.

Thinking back I took the long and winding road, this does exactly the same and is easier to understand.

Within List.Zip I’ve created 3 lists, using the List initializer { }
The first list contains the Labels, the second Hours and the third Dollars - just one thing to keep in mind - the items in each list must be in the same order! Like a zipper List.Zip brings the values together based on their position in the list all 1.2 are on the first position and get grouped together in a list and so on…


What I find seriously amazing is that you have received 3 PQ solutions :grinning: and I’m convinced there are many more ways to solve this!
That happens for DAX more frequent on the forum and I hope it will become a new trend for Power Query questions as well.
Kudos to @Heather and @Rajesh :+1:

@michellepace, let me know if you have any additional questions, I’d be happy to explain further if something is unclear.


Hi @michellepace

You can try Query Diagnostics.

Hi @Heather, @Rajesh,

Hope you guys forgive me but @michellepace question made me curious…

So I’ve used this method to time each of our Queries after increasing the number of records in the XLSX and then timed 10 executions for each of our queries.

You can check the results in this file…
e-DNA Unpivot Multiple Dual columns - Solutions+Timing x3.pbix (91.8 KB)

If you want to repeat the process, make the following modifications to the XLSX because it was too big to upload - copy the single_row_to_test data over into the seconde row on FullData and copy that single line down into all worksheet rows (1048576).
Add the full file path+name to the FileLocation parameter list and select it.

It will take some time to refresh so take the dog for a walk or watch an episode of your favorite TV show Also keep in mind that depending on your system, timings will differ with what I’ve recorded…

1 Like

@Melissa Thanks for sharing. This really helps.

Hi @Melissa, @michellepace

I just wanted to share with you my new solution which is more powerful than my previous solution. In this i created a custom column…output is table.

Just one step to get the required output… that’s it.

Unpivotting multiple columns.pbix (99.4 KB)

Hope this helps…

1 Like

Great one @Rajesh :+1:

That’s solution number 4, have you ever used the intrinsic #table before?

I am loving this thread. Great initial question by @michellepace, and excellent solutions and discussions by @Heather, @Melissa and @Rajesh. The “dueling DAX approach” threads are more common, but I definitely hope to see more of these for PQ. Have learned a lot from this exchange - thanks to all for the great content!

  • Brian


No. This is the first time… To be frank, I didn’t like my first solution…it is not easy to understand, more steps & also takes longer time…

I just got this idea when i see table definition from my PQ notes.

Hi @Rajesh,

Like the thought pattern and a really nice solution BTW. Did the speed test again and it comes in just a fraction of a second slower than List.Zip in most cases but the difference is negligible.

Amazing improvement compared to the first query. Kudos :+1:

1 Like

Hi @michellepace , did the response provided by @Melissa, @Heather and @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Melissa

When i tested most cases table is faster that List.

I think it depends on system performance…while running the quires.