Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
My workaround was to append all the second tables, and then merge that with the first table.
My workaround was to append all the second tables, and then merge that with the first table.