Unpivot multiple columns but not duplicate rows

[Niall_Buissing]

Hey guys,

Hopefully, you can help me with my question! Been trying for a few days now, and after a lot of frustration decided to see whether you guys can help me. I'm using Power Query (not BI) to transform data output from one system to an importable format for another system. However the structure of both files is not quite the same so that's where I need some help. 

#	Official name	Staff	Class
Test 1	Test morning	Staff_a, Staff_b, Staff_c	Class_a, Class_b
Test 2	Test afternoon	Staff_a	Class_a, Class_b, Class_c
Test 3	Test evening	Staff_a, Staff_b	Class_a

In my original file I have two columns with comma separated values. Both of these columns need to be unpivoted/transposed but without creating duplicate rows. Because the test still remains the same however instead of aligned horizontally the staff and class need to be aligned vertically.

If my query works correctly it needs to look the following:

#	Official name	Staff	Class
Test 1	Test morning	Staff_a	Class_a
null	null	Staff_b	Class_b
null	null	Staff_c	null
Test 2	Test afternoon	Staff_a	Class_a
null	null	null	Class_b
null	null	null	Class_c
Test 3	Test evening	Class_a	Staff_a
		Class_b

However after splitting the columns, unpivoting both of the columns it creates duplicates for each instance and looks the following (also tried to create two different queries and merge them but with the same effect):

#	Official name	Staff	Class
Test 1	Test morning	Staff_a	Class_a
null	null	Staff_b	Class_a
null	null	Staff_c	Class_a
null	null	Staff_a	Class_b
null	null	Staff_b	Class_b
null	null	Staff_c	Class_b

(I already know how to create the null values in the other columns, the main problem is with the duplicates in the columns Staff/Class).

Hopefully you guys know the trick on how to solve this, or if it is not even possible how I get closest to my desired result.

If you need any more information, please let me know! Thanks a lot for your help...

Regards,

Niall

[Niall_Buissing]

Thanks, exactly what I needed. You made my day! 

Kind regards,
Niall

[camargos88 · Accepted solution]

Hi @Niall_Buissing,

I've created this file as an example: Download PBIX 

I hope it helps.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[mjarroyo]

Hi Ricardo... I tried to see the file you attached but maybe it was removed. Could you please share with me the solution to this problem. I have the same issue. Thanks

MJArroyo