Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    My workaround was to append all the second tables, and then merge that with the first table.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    My workaround was to append all the second tables, and then merge that with the first table.