Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Magnus-CPH-DK
Helper II
Helper II

Problems with unpivoting multiple column value pairs

Hi everybody,

 

I have a table with contracts which frequently undergo inspections. Each inspection can contain up two 3 types of suppliers, each with their own respective supplier ID.

In the attached PBI file I have made two draft tables. The first "Before_Unpivot" is an exaple of my current dataset, where there is 1 column for each of the 3 types of supplier and 1 column for each of their respective supplier ID's. I need to transform the data into the table "After_Unpivot" where each inspection instead contain 3 rows and where the supplier and supplier ID consist of one column each instead of 6 columns. In the table "My_Own_Attept" I have tried to unpivot the columns myself. As you can see I am able to unpivot the 3 ID's and supplier types into 1 column each, but I still need the 3 supplier columns into one column. 

Below are 3 images of the tables in Power Query.

 

Anyone who can help me out?

Thanks a lot!

 

PS: I tried to find a way to attach my PBIX file, but I could not find any attachment options

 

Before_Unpivot:

 

 

MagnusCPHDK_2-1662974969484.png

 

 

After_Unpivot:

 

 

MagnusCPHDK_3-1662974995697.png

 

 

My_Own_Attempt:

 

 

MagnusCPHDK_4-1662975016425.png

 

 

 

 

 

1 ACCEPTED SOLUTION

FYI that I was not able to modify your query as it references a file on your local drive. However, FYI that this YoutTube video covers how to handle this scenario. There is a file you can download too, if needed.

 

Faster Data Transformations with List/Record M Functions - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Magnus-CPH-DK
Helper II
Helper II

Hi Pat,

Thanks a lot for your help! Hmm, alright - I'll try another file sharing solution next time.

Before I got a chance to watch your linked video, I actually managed to solve the issue myself in a quite simple way. I am not sure it might be the best solution for all similar problems, but for those who might be interested, this is how I did it:

 

1. I merged main supplier with main supplier ID, sub-supplier with sub-supplier IDand over-supplier with over-supplier ID (I used a pipe symbol as delimiter):

 

MagnusCPHDK_0-1663068322595.png

 

2. I unpivoted the three newly merged columns at once and changed the header names:

 

MagnusCPHDK_1-1663068399995.png

 

 

 

 

 

Magnus-CPH-DK
Helper II
Helper II

Here is the link to the PBIX file:

 

https://we.tl/t-ZiFpCbGjnB

FYI that I was not able to modify your query as it references a file on your local drive. However, FYI that this YoutTube video covers how to handle this scenario. There is a file you can download too, if needed.

 

Faster Data Transformations with List/Record M Functions - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.