Dec 05 2018
12:57 PM
- last edited on
Jul 12 2019
11:17 AM
by
TechCommunityAP
Dec 05 2018
12:57 PM
- last edited on
Jul 12 2019
11:17 AM
by
TechCommunityAP
How can I replace the values in this first column
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.
Dec 05 2018 01:12 PM
@Imke Feldmann - I wonder if you have a suggestion :) ?
Dec 05 2018 01:56 PM
Hi Ian,
If I understood you correctly the transformation is like this
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
Dec 06 2018 08:43 AM
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"
Dec 06 2018 02:18 PM
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
Feb 04 2021 09:00 AM
Feb 04 2021 09:00 AM
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
Feb 04 2021 10:34 AM