Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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:
After_Unpivot:
My_Own_Attempt:
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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):
2. I unpivoted the three newly merged columns at once and changed the header names:
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
11 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |