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

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

7 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

 

can you please tell where to write this script. I have the same exact requirement. Trying to understand what script is this and how to execute? Please advise

can you please tell where to write this script. I have the same exact requirement. Trying to understand what script is this and how to execute? Please advise@Sergei Baklan 

Please ignore, I found it is Power Query and it all worked.

Thanks for the solution