Problems with unpivoting multiple column value pairs

[Magnus-CPH-DK]

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:

[Magnus-CPH-DK]

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:

[Magnus-CPH-DK]

Here is the link to the PBIX file:

https://we.tl/t-ZiFpCbGjnB

[mahoneypat · 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