Unpivot to create master table

[Anonymous]

Hello all,

I am trying to find the most efficient way to do this in Power Query and right now the way I'm doing it is pretty messy.

I have a query that contains different columns like "Created By", "Resolved By", "Assigned To" that contain names of users.

I also have the equivalent columns "Created By GUID", "Resolved By GUID", "Assigned To GUID" that contains their corresponding unique identifiers. It looks something like this:

Created By	Resolved By	Assigned To	Created By GUID	Resolved By GUID	Assigned To GUID
John		John	A1B2		A1B2
Mark	John	Lauren	C3D4	A1B2	E5F6
Lauren		Jill	E5F6		G7H8
Jill	Lauren	Lauren	G7H8	E5F6	E5F6

I want the final table to contain the unique names and their corresponding GUIDs:

Name	GUID
John	A1B2
Mark	C3D4
Lauren	E5F6
Jill	G7H8

I tried to separate the tables in user names only and GUIDs only, unpivotted them, removed the duplicates, added an index column then merged the queries back into one. That seems inefficient and the indexes might not be matching with the rows due to sorting. Is there a better way of doing this?

[Vijay_A_Verma · Accepted solution]

Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately). Though we could have run List.Distinct when generating List1 and List2 but if two persons with same name and different GUID, then this will create issues. Hence, I am performing distinct operation at table level only.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQIiKMvR0MkIIgBmxepEK/kmFmUjFPgklhalghjOxi4mCA2upm5mYNVweZCWzJwcmBxYwN3cwwKsCioDVwxngFXAtUBMjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created By" = _t, #"Resolved By" = _t, #"Assigned To" = _t, #"Created By GUID" = _t, #"Resolved By GUID" = _t, #"Assigned To GUID" = _t]),
    List1 = Source[Created By]&Source[Resolved By]&Source[Assigned To],
    List2 = Source[Created By GUID]&Source[Resolved By GUID]&Source[Assigned To GUID],
    #"Generate Table" = Table.FromColumns({List1, List2},{"Name", "GUID"}),
    #"Removed Duplicates" = Table.Distinct(#"Generate Table"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Name] <> ""))
in
    #"Filtered Rows"

[BA_Pete]

Hi @Anonymous ,

Here's an option. It's not particularly elegant, but it's quick and simple:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUVIAYijT0dDJCCoCZsbqRCv5JhZlI1T4JJYWpYIYzsYuJggdrqZuZmDVcHmwqZk5OTBJiIi7uYcFWB1UCq4czgCrgOuBmBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created By" = _t, #"Resolved By" = _t, #"Assigned To" = _t, #"Created By GUID" = _t, #"Resolved By GUID" = _t, #"Assigned To GUID" = _t]),
    selectCreatedBy = Table.SelectColumns(Source,{"Created By", "Created By GUID"}),
    renCreated = Table.RenameColumns(selectCreatedBy,{{"Created By", "Name"}, {"Created By GUID", "GUID"}}),
    selectResolvedBy = Table.SelectColumns(Source,{"Resolved By", "Resolved By GUID"}),
    renResolved = Table.RenameColumns(selectResolvedBy,{{"Resolved By", "Name"}, {"Resolved By GUID", "GUID"}}),
    selectAssignedTo = Table.SelectColumns(Source,{"Assigned To", "Assigned To GUID"}),
    renAssigned = Table.RenameColumns(selectAssignedTo,{{"Assigned To", "Name"}, {"Assigned To GUID", "GUID"}}),
    appendSegments = Table.Distinct(Table.Combine({renCreated, renResolved, renAssigned})),
    filterEmptyGUID = Table.SelectRows(appendSegments, each ([GUID] <> " "))
in
    filterEmptyGUID

It basically splits the original table into three segments then appends them. This ensures you account for all name/GUID combinations.

Pete