Forum Discussion

audian_ben's avatar
audian_ben
Frequent Visitor
1 year ago
Solved

Find and replace the GUID's in a column of lists from another table

Hey all,

 

I have a case where I my Bingfoo and Googlefoo is failing me.  I have 2 tables that I am trying to rectify.  The first is a list of users and it has a column of lists that contain a number of GUIDs for each user, and each user has a different number of total GUIDs.  I have then created an Excel doc to be able to rectify the GUID to a friendly name.  With the number of each GUID each user has, would make this difficult to stay dynamic if I just did the expand list into columns and then merged because one user may have 1 GUID and another could have 20.  And example of what I have is:

 

User list:

UserIDGUID's
user1guid1
user2guid2, guid3, guid4
user3guid1, guid2, guid3, guid4
user4guid3, guid4

 

GUID table:

guid1friendly1
guid2friendly2
guid3friendly3
guid4friendly4

 

Desired outcome:

User1friendly1
User2friendly2, friendly3, friendly4
User3friendly1, friendly2, friendly3, friendly4
User4friendly3, friendly 4

So my dilemma is how to do this with a power query unless your big brains have another way to do this better

 

Thank you for your insights

Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    let
    user_list = Excel.CurrentWorkbook(){[Name="user_list"]}[Content], 
    guid_table = Function.Invoke(
        Record.FromList, 
        List.Reverse(Table.ToColumns(Excel.CurrentWorkbook(){[Name="guid_table"]}[Content]))
    ), 
    result = Table.TransformColumns(
        user_list, 
        {"guid", (x) => Text.Combine(
            List.Transform(
                Text.Split(x, ", "), 
                (w) => Record.FieldOrDefault(guid_table, w, w)
            ), 
            ", "
        )}
    )
in
    result

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Your user list example has formatting inconsistencies (comma missing for user2). Are these to be expected with the real data as well?

     

    What is your expected outcome based on the sample you provided?

  • audian_ben's avatar
    audian_ben
    Frequent Visitor
    1 year ago

    updated my post with the correction to my typo.  and I have added what my desired outcome is. 

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    1 year ago
    let
    user_list = Excel.CurrentWorkbook(){[Name="user_list"]}[Content], 
    guid_table = Function.Invoke(
        Record.FromList, 
        List.Reverse(Table.ToColumns(Excel.CurrentWorkbook(){[Name="guid_table"]}[Content]))
    ), 
    result = Table.TransformColumns(
        user_list, 
        {"guid", (x) => Text.Combine(
            List.Transform(
                Text.Split(x, ", "), 
                (w) => Record.FieldOrDefault(guid_table, w, w)
            ), 
            ", "
        )}
    )
in
    result