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.
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”?
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)
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
add an index column (great for debugging down the line so you can prove that everything unpivoted properly.
unpivot Hours columns
2a. I handled the column rename in step one, if you do not want to do that - rename columns
unpivot Dollars columns
3a. again, rename handled in the unpivot step, if you do not do this - rename columns to keep track
clean up the descriptor for the column with the Dollars_OT@ values (you need to match this against the OT @ columns)
add conditional column comparing the two descriptor columns, use this to either return the Dollar values or a null value
filter nulls out of column in step 5, and cleanup unneeded columns
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).
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 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
@michellepace, let me know if you have any additional questions, I’d be happy to explain further if something is unclear.
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.
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…
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!
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.
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!
and I’m convinced there are many more ways to solve this!
