Pivot / Merge question

[KS12]

I'm looking to keep this same matrix visualization structure, which is currently created using just data set 1.  I need to merge this with the data from data set 2.

I originally planned to just

1. pivot data set 1 to have each "generic role" in an individual column to result in 1 row for the each Scrum Team/Project

2. then merge queries using "Project Name" from data set 1 & "US Tech Project Name" from data set 2 as the matching column

3. unpivot the all "generic role" columns & "staff name" columns to create 2 columns 1 = generic roles & 2 = staff names

Unfortunately, this did not work since there are multiple staff names for the same role.  Any suggestions?

data set 1 - Roster sheet

data set 2 - Project sheet

End goal for merging data sets (green = data set 1 & blue = data set 2)

[v-yadongf-msft · Accepted solution]

Hi @KS12 ,

Transform data in Power Query: select the three columns inside the red box - unpivot columns.

You will get a table like this:

Rename for every column:

Append queries as new:

You will get a table like this:

Replace "Data Integrity Review - Enhancements & Support" with "Spartans".

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.