Generate Lookup Table on Multiple columns All of which are in pairs of {ID, Title}

[TimothyJames]

I have data that looks like below all in 1 table.

Horizontally, the values are not distinct.

Vertically, the values are not distinct.

1 {Title AND ID} can be in multiple rows,

1 {Title AND ID} can be in multiple columns,

AssigneeTitle1	AssigneeID1	AssigneeTitle2	AssigneeID2	AssigneeTitle3	AssigneeID3
Nick M	244	Nick M	244	Nick M	244
null	null	null	null	null	null
null	null	null	null	null	null
Nick M	244	Ken D	344	Mason W	211
null	null	null	null	null	null
null	null	null	null	null	null
Ken D	344	null	null	Ken D	344
null	null	null	null	null	null
Mason W	211	null	null	Gary B	225

How can I produce something like the following

Nick M	244
Ken D	344
Mason W	211
Gary B	225

Thank you

[v-shex-msft · Accepted solution]

Hi @TimothyJames,

You can enter to query editor and do 'unpivot column' on these fields, then you can use attribute and value fields to accurately control which value is returned.

Unpivot columns - Power Query | Microsoft Docs

Regards,

Xiaoxin Sheng

[amitchandak]

@TimothyJames , refer if the attached file can help

[TimothyJames]

@amitchandak  thanks for the reply, How do I do it if all the columns are in their own separate columns, but 1 table.