Unpivot Multiple Column Pairs In Power Query

We are going to unpivot multiple column combinations in this video. This is part of the Unpivot Variation series which is all about turning your data into a proper tabular format that is suitable for analysis. I will also explore how we can use List.Zip() to unpivot sets of data in Power Query.
**** Video Details ****
0:35 source table
1:13 data pairs and 3 overtime types
2:15 adding custom column - List.Zip
4:11 how to remove value columns
4:39 expand to new rows and generated 3 records
4:52 extract values function
5:10 split by delimiter
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - Power BI Resources - enterprisedna.c...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....

[@niyazrafatkhan8364]

Dear Friend, Not only you have shown a way to ease my work, you leave us all indebted by your high quality knowledge. Thank you so much!!!

[@jennakilgore5570]

This was EXACTLY what I was trying to accomplish - great video, thank you!

[@EnterpriseDNA]

Loving this high-quality PQ content Melissa, well done!

[@charles_mclean]

This was very interesting and exactly what I needed. Videos like this is why I stay humble and never assume I'm a pro. Thank you

[@EnterpriseDNA]

Hi Charles Mc Lean, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@PatrickWolfM]

Agreed. Amazing video

[@joanneosborne2428]

You're the best and very well explained! Melissa. Thanks :)

[@gabriellajager3316]

Hi Melissa! Exactly what i have been searching for some days. Sometimes it is hard to describe and find in google what i want, but finally I have found this! Thanks for this great solution, your explanation is clear.

[@EnterpriseDNA]

Hi Gabriella, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

Dude! You´re a wizard. You have no idea how much you helped my at my job. Thanks!

[@EnterpriseDNA]

Hi Jaime Suárez, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@marksimmonds4818]

Exact;y what I was looking for! Thank you!

[@Bondoz007]

Thanks so much excellent tip - I work with multiple payroll templates and it drives me nuts! This is perfect - thanks

[@MareliAugustyn]

Exactly what I needed, thank you!

[@EnterpriseDNA]

We’re glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming data skills and AI tutorials, and announcements. Cheers!

[@johngalt533]

That's amazing!! Keep going - you're doing fantastic things!

[@EnterpriseDNA]

Hi John Galt, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@iphner43]

Shouldn't you have worked harder and earned your ability to do this yourself instead of relying upon ingenuity, creativity and hard work of others? John Galt would certainly be disappointed in your lack of personal responsibility and self-reliance!!!

[@10ozGold]

This is genius! Love all your videos Melissa. Thank-you so much.

[@EnterpriseDNA]

Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@jamespratt1656]

Many thanks for this, massive help to my client project.

[@EnterpriseDNA]

Hi James, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@krittaponopdc7649]

Thank, this helped me a lot.

[@EnterpriseDNA]

Hi Krittapon, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@joramao]

I was struggling a lot, but you just saved me. Thanks so much

[@Bondoz007]

Just an additional tip - don't know why but when I did 4:14 "Remove Columns" I couldn't get the rest of the steps to work. However, by doing Remove Other Columns it worked fine. ❤

[@naveenpatil1997]

This has been of great help..thanks a lot!

[@EnterpriseDNA]

Hi Naveen, we're glad that you found value in this video! You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers! Enterprise DNA Channel: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA.

[@ar3home]

Creative solution. Would love to see a performance comparison with this method vs. unpivot.

[@EnterpriseDNA]

Hi Anthony Riccio, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@richardliu7780]

THANK YOU

[@yewchoob85]

A-Mazing!

[@EnterpriseDNA]

Hi Ben, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@umairmansuri5822]

Thanks it was helpful :)

[@EnterpriseDNA]

Hi umair, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@MultiZlim]

Sorry i'm pretty noob to PBI. I have 4 columns: Forecast 1, Budget 1, Forecast 2, Budget 2. How can i unpivot this like : Forecast1+ Budget 1 = Column 1, Forecast1+ Budget 1 values = Column 2, Forecast 2+ Budget 2 = Column 3, Forecast2+ Budget 2 values = Column 4?

[@bwaightstubesyou]

Thanks! just help me solve this exact problem

[@EnterpriseDNA]

Hi bwaightstubesyou, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@tomaskavicky]

excellent!!!!!!!

[@EnterpriseDNA]

Hi Tomas, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@balaji_906]

Superb information.

[@EnterpriseDNA]

Hi Balaji M, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@Faadiz]

@EnterpriseDNA I need help with the following...not an expert so still trying to understand the concept. I have 8 columns containing Inventory Qty and Inventory USD 4 columns for Qty i.e. QTY --> 0-6 Old | 7-12 | 13-29 | 30+ 4 columns for USD for the same qty above USD --> 0-6 Old | 7-12 | 13-29 | 30+ How can i get in following way Col 1 = Duration (0-6 Old | 7-12 | 13-29 | 30+ Row Specific) Col 2 = Inv Qty based on Col 1 Col 3 = Inv USD based on Inv Qty in Col 2 based on Duration in Col 1 I tried using Reference table and removing and unpivoting. Works fine but i am having issues in SScheduled Data refresh on *Index Column*

[@EnterpriseDNA]

Hello Fahd Akhtar, thank you for posting your query onto our channel. Well, it's always a little bit difficult to judge and provide the results without looking at the working PBIX file since we don't have the idea of the data structure and scenario that you've in your file. We encourage you to please write back to us, onto our Community Forum by providing the working of the PBIX file along with the description of the problem that you're facing and the results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

[@granand]

Thank you very much. This is useful. I have a problem, my data runs in thousands, Please can you advise if there is a easy way than manual typing of list.zip({"OT@1.2", "OT@1.33", "OT@1.55" .. goes on for many many records, Do I have do any offline work to create this message or is there some function which can do this, say if a few more records are added.

[@EnterpriseDNA]

Hello granand, thank you for posting your query onto our channel. Well, it's always a little bit difficult to judge and provide the results without looking at the PBIX file and structure of the data. We encourage you to please write back to us onto our Community Forum by providing the working of the PBIX file along with the description of the problem that you're facing as well as the results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Also this video covered the scenario where you've number of columns and not the number of rows. The number of rows shouldn't be an issue if you've this type of data. If there're multiple columns in your file than this trick is more than efficient in any case. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

[@granand]

@@EnterpriseDNA Thank you for responding back and I will surely take help from your forum. Yes I have about 25-30 columns or even more. :-(

[@MarioYouTube1]

Thank you. Exactly what I needed!

[@EnterpriseDNA]

Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@McFloater1200]

how do you do the reverse? meaning your output to the tabulated format?

[@EnterpriseDNA]

Hello McFloater1200, In the given video, the result is already evaluated into the tabulated format. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our KZbin channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/

[@mudassirsyedrashidali9787]

Amazing trick .. hope to see more of M Code

[@EnterpriseDNA]

Hi Mudassir Syed Rashid Ali, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@Bondoz007]

Thanks

[@EricaDyson]

As usual .. Wunderbar. So clearly explained and so relevant! Thanks a lot. Tak!

[@EnterpriseDNA]

Hi Erica Dyson, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@anusachan6526]

Lady, you are awesome !

[@EnterpriseDNA]

Hi Anu, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@granand]

Thank you, I have added to your course. Can I ask a question, why is most Power BI developers focus is DAX when I read somewhere that it is recommended that most of the transformations better be done in the transform data (M language) or did I read wrong ? Please advise.

[@EnterpriseDNA]

Hello granand, thank you for posting your query onto our channel. Well, we also encourage the same concept/technique be it into our videos or onto our forum. If the analysis is static than it's always recommended to do the transformations in the Query Editor whilst on the other hand if the analysis is dynamic than it's always recommended to use the DAX. Also if the developer wants to improve the performance of the report it's always recommended to do the transformations into the source. That is, in the case of Excel/CSV, it'll be in Query Editor and in case of SQL or server based data, it can be done directly into the source, if possible, otherwise Power Query is the second best option in that case. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this explanation useful and meets your requirements that you've been looking for. Happy Learning!!! forum.enterprisedna.co/

[@MuhammadAhmed-wk4zh]

Thanks for making this. where we can find the source file of data which is used in above video. So we can practice on it.

[@EnterpriseDNA]

Hi Muhammad Ahmed, thanks for posting your comment in the video. All pbix file/datasets are available for download in the Enterprise DNA Online, which is accessible via Membership. Find link below. Thanks! portal.enterprisedna.co/

[@alexandreletourneux9581]

M Code sorcery :p Thanks & well done. Alexandre

[@EnterpriseDNA]

Hi Alexandre Letourneux, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@dfoster7001]

Perfect! Thank you!

[@EnterpriseDNA]

Hi Dwayne, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!