Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Unpivot to create master table

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 ByResolved ByAssigned ToCreated By GUIDResolved By GUIDAssigned To GUID
John JohnA1B2 A1B2
MarkJohnLaurenC3D4A1B2E5F6
Lauren JillE5F6 G7H8
JillLaurenLaurenG7H8E5F6E5F6

 

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

NameGUID
JohnA1B2
MarkC3D4
LaurenE5F6
JillG7H8

 

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?

Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
    BA_Pete
    Super User
    4 years ago

    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