Home

Replace values in a given column (column is comma separated id's) with lookupvalue from a query

%3CLINGO-SUB%20id%3D%22lingo-sub-295575%22%20slang%3D%22en-US%22%3EReplace%20values%20in%20a%20given%20column%20(column%20is%20comma%20separated%20id's)%20with%20lookupvalue%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295575%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20replace%20the%20values%20in%20this%20first%20column%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20367px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61500i60DE9FF5070BAB9E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222018-12-05_14-49-51.png%22%20title%3D%222018-12-05_14-49-51.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20lookup%20value%20from%20this%20other%20query%3F%20Ideally%20it%20comma%20separates%20the%20values%2C%20skipping%20anything%20that's%20null%20in%20the%20lookup%20(so%20no%20blank%20spaces%20between%20commas.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20234px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61502iBD5DE6A47161BCB2%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%222018-12-05_14-51-18.png%22%20title%3D%222018-12-05_14-51-18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-295575%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-297085%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20values%20in%20a%20given%20column%20(column%20is%20comma%20separated%20id's)%20with%20lookupvalue%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-297085%22%20slang%3D%22en-US%22%3E%3CP%3EIan%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%20Index%20to%20avoid%20possible%20errors%20in%20enumeration%20and%20extract%20list%20to%20combine%20from%20GroupByIndex%5BCount%5D%20column.%20You%20don't%20need%20filter%20%5Bemail%5D%20then.%20Like%20this%20and%20attached%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22WorkTracker%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20RemoveOtherColumns%20%3D%20Table.SelectColumns(Source%2C%7B%22ID%22%2C%20%22Team%20Members%22%7D)%2C%0A%20%20%20%20AddIndex%20%3D%20Table.AddIndexColumn(RemoveOtherColumns%2C%20%22Index%22%2C%200%2C%201)%2C%0A%20%20%20%20TextsToLists%20%3D%20Table.AddColumn(AddIndex%2C%22Custom%22%2C%20each%20Text.SplitAny(%5BTeam%20Members%5D%2C%22%2C%22))%2C%0A%20%20%20%20ListsToRows%20%3D%20Table.ExpandListColumn(TextsToLists%2C%20%22Custom%22)%2C%0A%20%20%20%20IDasNumber%20%3D%20Table.TransformColumnTypes(ListsToRows%2C%7B%7B%22Custom%22%2C%20Int64.Type%7D%7D)%2C%0A%20%20%20%20MergeWithLookupTable%20%3D%20Table.NestedJoin(IDasNumber%2C%7B%22Custom%22%7D%2C%0A%20%20%20%20%20%20%20%20UserInfo%2C%7B%22user%22%7D%2C%22UserInfo%22%2CJoinKind.LeftOuter)%2C%0A%20%20%20%20GetValues%20%3D%20Table.ExpandTableColumn(MergeWithLookupTable%2C%20%22UserInfo%22%2C%20%7B%22email%22%7D%2C%20%7B%22email%22%7D)%2C%0A%20%20%20%20GroupByIndex%20%3D%20Table.Group(GetValues%2C%20%7B%22Index%22%7D%2C%20%7B%7B%22Count%22%2C%20each%20_%2C%20type%20table%7D%7D)%2C%0A%20%20%20%20ValuesToCSV%20%3D%20Table.AddColumn(GroupByIndex%2C%20%22AT%20Team%20Members%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.Combine(GroupByIndex%5BCount%5D%7B%5BIndex%5D%7D%5Bemail%5D%2C%22%2C%22))%2C%0A%20%20%20%20ExtractID%20%3D%20Table.AddColumn(ValuesToCSV%2C%20%22ID%22%2C%20each%20%5BCount%5D%5BID%5D%7B0%7D)%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.SelectColumns(ExtractID%2C%7B%22ID%22%2C%20%22AT%20Team%20Members%22%7D)%0Ain%0A%20%20%20%20RemoveUnused%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-296900%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20values%20in%20a%20given%20column%20(column%20is%20comma%20separated%20id's)%20with%20lookupvalue%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-296900%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20was%20very%20helpful%2C%20thank%20you.%20I%20was%20having%20just%20a%20few%20more%20issues%20with%20it%20as-is.%20Mainly%2C%20I%20needed%20to%20adjust%20the%20ValuesToCSV%20line.%20Before%20I%20did%20so%2C%20it%20wasn't%20putting%20the%20right%20replaced%20values%20in%20the%20rows%2C%20and%20error-ring%20out%20on%20many.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EValuesToCSV%20%3D%20Table.AddColumn(GroupByIndex%2C%20%22Custom%22%2C%20each%20Text.Combine(Table.ToList(Table.RemoveColumns(%5BCount%5D%2C%20%22ID%22))%2C%22%2C%22))%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20full%20thing%20looks%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3Blet%3CBR%20%2F%3ESource%20%3D%20%23%22Work%20Trackr%22%2C%3CBR%20%2F%3ERemovedOtherColumns%20%3D%20Table.SelectColumns(Source%2C%7B%22ID%22%2C%20%22Team%20Members%22%7D)%2C%3CBR%20%2F%3ETextsToLists%20%3D%20Table.AddColumn(RemovedOtherColumns%2C%22Custom%22%2C%20each%20Text.SplitAny(%5BTeam%20Members%5D%2C%22%2C%22))%2C%3CBR%20%2F%3EListsToRows%20%3D%20Table.ExpandListColumn(TextsToLists%2C%20%22Custom%22)%2C%3CBR%20%2F%3EIDasNumber%20%3D%20Table.TransformColumnTypes(ListsToRows%2C%7B%7B%22Custom%22%2C%20Int64.Type%7D%7D)%2C%3CBR%20%2F%3EMergeWithLookupTable%20%3D%20Table.NestedJoin(IDasNumber%2C%7B%22Custom%22%7D%2CUserInfo%2C%7B%22user%22%7D%2C%22UserInfo%22%2CJoinKind.LeftOuter)%2C%3CBR%20%2F%3EGetValues%20%3D%20Table.ExpandTableColumn(MergeWithLookupTable%2C%20%22UserInfo%22%2C%20%7B%22email%22%7D%2C%20%7B%22email%22%7D)%2C%3CBR%20%2F%3E%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(GetValues%2C%20each%20(%5Bemail%5D%20%26lt%3B%26gt%3B%20%22%22))%2C%3CBR%20%2F%3E%23%22Removed%20Columns%22%20%3D%20Table.RemoveColumns(%23%22Filtered%20Rows%22%2C%7B%22Custom%22%2C%20%22Team%20Members%22%7D)%2C%3CBR%20%2F%3EGroupByIndex%20%3D%20Table.Group(%23%22Removed%20Columns%22%2C%20%7B%22ID%22%7D%2C%20%7B%7B%22Count%22%2C%20each%20_%2C%20type%20table%7D%7D)%2C%3CBR%20%2F%3EValuesToCSV%20%3D%20Table.AddColumn(GroupByIndex%2C%20%22Custom%22%2C%20each%20Text.Combine(Table.ToList(Table.RemoveColumns(%5BCount%5D%2C%20%22ID%22))%2C%22%2C%22))%2C%3CBR%20%2F%3E%23%22Removed%20Columns1%22%20%3D%20Table.RemoveColumns(ValuesToCSV%2C%7B%22Count%22%7D)%2C%3CBR%20%2F%3E%23%22Renamed%20Columns%22%20%3D%20Table.RenameColumns(%23%22Removed%20Columns1%22%2C%7B%7B%22Custom%22%2C%20%22AT%20Team%20Members%22%7D%7D)%3CBR%20%2F%3Ein%3CBR%20%2F%3E%23%22Renamed%20Columns%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295605%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20values%20in%20a%20given%20column%20(column%20is%20comma%20separated%20id's)%20with%20lookupvalue%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295605%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ian%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20you%20correctly%20the%20transformation%20is%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20326px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61509iA6FDA2E73BF81CCF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20so%2C%20the%20script%20could%20be%3C%2FP%3E%0A%3CPRE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20TextsToLists%20%3D%20Table.AddColumn(Source%2C%20%22Custom%22%2C%20each%20Text.SplitAny(%5Bcol%5D%2C%22%2C%22))%2C%0A%20%20%20%20AddIndex%20%3D%20Table.AddIndexColumn(TextsToLists%2C%20%22Index%22%2C%200%2C%201)%2C%0A%20%20%20%20ListsToRows%20%3D%20Table.ExpandListColumn(AddIndex%2C%20%22Custom%22)%2C%0A%20%20%20%20IDasNumber%20%3D%20Table.TransformColumnTypes(ListsToRows%2C%7B%7B%22Custom%22%2C%20Int64.Type%7D%7D)%2C%0A%20%20%20%20MergeWithLookupTable%20%3D%20Table.NestedJoin(IDasNumber%2C%7B%22Custom%22%7D%2CTable2%2C%7B%22ID%22%7D%2C%22Table2%22%2CJoinKind.LeftOuter)%2C%0A%20%20%20%20GetValues%20%3D%20Table.ExpandTableColumn(MergeWithLookupTable%2C%20%22Table2%22%2C%20%7B%22Value%22%7D%2C%20%7B%22Value%22%7D)%2C%0A%20%20%20%20GroupByIndex%20%3D%20Table.Group(GetValues%2C%20%7B%22Index%22%7D%2C%20%7B%7B%22Count%22%2C%20each%20_%2C%20type%20table%7D%7D)%2C%0A%20%20%20%20ValuesToCSV%20%3D%20Table.AddColumn(GroupByIndex%2C%20%22Custom%22%2C%20each%20Text.Combine(GroupByIndex%5BCount%5D%7B%5BIndex%5D%7D%5BValue%5D%2C%22%2C%22))%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.SelectColumns(ValuesToCSV%2C%7B%22Custom%22%7D)%0Ain%0A%20%20%20%20RemoveUnused%3C%2FPRE%3E%0A%3CP%3EPlease%20see%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295582%22%20slang%3D%22en-US%22%3ERe%3A%20Replace%20values%20in%20a%20given%20column%20(column%20is%20comma%20separated%20id's)%20with%20lookupvalue%20from%20a%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F6807%22%20target%3D%22_blank%22%3E%40Imke%20Feldmann%3C%2FA%3E%26nbsp%3B-%20I%20wonder%20if%20you%20have%20a%20suggestion%20%3A)%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

How can I replace the values in this first column

2018-12-05_14-49-51.png 

 

With the lookup value from this other query? Ideally it comma separates the values, skipping anything that's null in the lookup (so no blank spaces between commas.

2018-12-05_14-51-18.png

4 Replies

Hi Ian,

 

If I understood you correctly the transformation is like this

image.png

If so, the script could be

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextsToLists = Table.AddColumn(Source, "Custom", each Text.SplitAny([col],",")),
    AddIndex = Table.AddIndexColumn(TextsToLists, "Index", 0, 1),
    ListsToRows = Table.ExpandListColumn(AddIndex, "Custom"),
    IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
    MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},Table2,{"ID"},"Table2",JoinKind.LeftOuter),
    GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "Table2", {"Value"}, {"Value"}),
    GroupByIndex = Table.Group(GetValues, {"Index"}, {{"Count", each _, type table}}),
    ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(GroupByIndex[Count]{[Index]}[Value],",")),
    RemoveUnused = Table.SelectColumns(ValuesToCSV,{"Custom"})
in
    RemoveUnused

Please see attached

That was very helpful, thank you. I was having just a few more issues with it as-is. Mainly, I needed to adjust the ValuesToCSV line. Before I did so, it wasn't putting the right replaced values in the rows, and error-ring out on many.

 

ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(Table.ToList(Table.RemoveColumns([Count], "ID")),",")),

 

So the full thing looks like:

 let
Source = #"Work Trackr",
RemovedOtherColumns = Table.SelectColumns(Source,{"ID", "Team Members"}),
TextsToLists = Table.AddColumn(RemovedOtherColumns,"Custom", each Text.SplitAny([Team Members],",")),
ListsToRows = Table.ExpandListColumn(TextsToLists, "Custom"),
IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},UserInfo,{"user"},"UserInfo",JoinKind.LeftOuter),
GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "UserInfo", {"email"}, {"email"}),
#"Filtered Rows" = Table.SelectRows(GetValues, each ([email] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Team Members"}),
GroupByIndex = Table.Group(#"Removed Columns", {"ID"}, {{"Count", each _, type table}}),
ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(Table.ToList(Table.RemoveColumns([Count], "ID")),",")),
#"Removed Columns1" = Table.RemoveColumns(ValuesToCSV,{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "AT Team Members"}})
in
#"Renamed Columns"

Ian,

 

I'd use Index to avoid possible errors in enumeration and extract list to combine from GroupByIndex[Count] column. You don't need filter [email] then. Like this and attached

let
    Source = Excel.CurrentWorkbook(){[Name="WorkTracker"]}[Content],
    RemoveOtherColumns = Table.SelectColumns(Source,{"ID", "Team Members"}),
    AddIndex = Table.AddIndexColumn(RemoveOtherColumns, "Index", 0, 1),
    TextsToLists = Table.AddColumn(AddIndex,"Custom", each Text.SplitAny([Team Members],",")),
    ListsToRows = Table.ExpandListColumn(TextsToLists, "Custom"),
    IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
    MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},
        UserInfo,{"user"},"UserInfo",JoinKind.LeftOuter),
    GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "UserInfo", {"email"}, {"email"}),
    GroupByIndex = Table.Group(GetValues, {"Index"}, {{"Count", each _, type table}}),
    ValuesToCSV = Table.AddColumn(GroupByIndex, "AT Team Members",
        each Text.Combine(GroupByIndex[Count]{[Index]}[email],",")),
    ExtractID = Table.AddColumn(ValuesToCSV, "ID", each [Count][ID]{0}),
    RemoveUnused = Table.SelectColumns(ExtractID,{"ID", "AT Team Members"})
in
    RemoveUnused