Forum Discussion
Use a parameter in a function to merge tables
I have two sets of tables I want to merge together: CountryCodeFromdRofus and S01-General, S02-Parking, S03...
I created a function to be able to repeat the merge tables:
CountryCodeFromdRofus and S01-General,
CountryCodeFromdRofus and S02-Parking,
CountryCodeFromdRofus and S03...
.... 20+ more merges
let
Source = (subGrp) => let
Source = CountryCodeFromdRofus,
filterGrp = Table.SelectRows(Source, each Text.StartsWith([Group], subGrp)),
replaceNull = Table.ReplaceValue(filterGrp,null,"",Replacer.ReplaceValue,{"Value"}),
addName = Table.AddColumn(replaceNull, "Name", each [CTRY] & " - " & [Group] & " - " & [Code]),
removeCode = Table.RemoveColumns(addName,{"Group"}),
orderCol= Table.ReorderColumns(removeCode,{"Name", "CTRY", "Code", "Value", "Country"}),
mergeImport = Table.NestedJoin(orderCol, {"Name"}, #"S01-General", {"Name"}, "S01-General", JoinKind.LeftOuter),
expandImport = Table.ExpandTableColumn(mergeImport, "S01-General", {"NewValue"}, {"NewValue"}),
removeOtherCol = Table.SelectColumns(expandImport,{"Name", "Item No", "NewValue"})
in
removeOtherCol
in
Source
I created a function parameter subGrp to filter the first table. The subGrp parameter is also the name of the second table.
I also want to use it to merge the two tables. When I replace the text values (e.g. "S01-General") with the parameter, I get the error cannot convert value "#S01-General" to Table.
mergeImport = Table.NestedJoin(orderCol, {"Name"}, subGrp, {"Name"}, SubGrp, JoinKind.LeftOuter),
expandImport = Table.ExpandTableColumn(mergeImport, subGrp, {"NewValue"}, {"NewValue"}),
My ultimate goal is to list the Item No from the first table, and the New Value from the second table.
I tried #subGrp, "#" & subGrp and "#"" &subGrp & """ to no success.
Please advise.
Thank you.
- Anonymous4 years ago
My workaround was to append all the second tables, and then merge that with the first table.
1 Reply
- AnonymousNot applicable
My workaround was to append all the second tables, and then merge that with the first table.