Forum Discussion
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:
After_Unpivot:
My_Own_Attempt:
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
3 Replies
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
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:
