Forum Discussion
Merging queries based on multiple conditions
Hello, I'm having difficultly merging two tables through the merge query function. Here's my situation: Table A has numerous fields, but the ones of interest are [First Name], [Last Name], [C...
When I link both fields into a relationship, I can only link it as a Many to One.
Then the error I get on Table C is "The Column with the name of 'Client ID' Already Exists in Table C.
That's not easy at all.
I've prepared a function for you because if you are a beginner, you will have difficulties to follow/execute the steps. Just copy this code into the advanced editor and give the query the name "MyFunction". Then call it and fill in the 3 parameter: Reference to 1st and 2nd table and a list with column names :
This should result in a code like this: MyFunctionName(TableA, TableB, {"Client ID", "Personal ID", "Concatenated First/DOB"})
(Table1 as table, Table2 as table, ListOfFieldNames) =>
let
Source = Table1,
#"Added Index" = Table.AddIndexColumn(Source, "Index1", 0, 1),
Unpivot1 = Table.Unpivot(#"Added Index", ListOfFieldNames , "Attribute", "Value"),
Source2 = Table2,
#"Added Index2" = Table.AddIndexColumn(Source2, "Index2", 0, 1),
Unpivot2 = Table.Unpivot(#"Added Index2", ListOfFieldNames , "Attribute", "Value"),
Source3 = Table.NestedJoin(Unpivot1,{"Attribute", "Value"},Unpivot2,{"Attribute", "Value"},"Table2",JoinKind.FullOuter),
#"Renamed Columns" = Table.RenameColumns(Source3,{{"Attribute", "Attribute0"}, {"Value", "Value0"}}),
Expand = Table.ExpandTableColumn(#"Renamed Columns", "Table2", List.Difference(Table.ColumnNames(Unpivot2), ListOfFieldNames), List.Transform(List.Difference(Table.ColumnNames(Unpivot2), ListOfFieldNames), each _&"_")),
SortAndBuffer = Table.Buffer(Table.Sort(Expand,{{"Index1", Order.Descending}, {"Index2_", Order.Descending}})),
#"Filtered Rows" = Table.SelectRows(SortAndBuffer, each ([Index1] <> null)),
MatchesFromFirstTable = Table.Distinct(#"Filtered Rows", {"Index1"}),
Custom1 = Expand,
#"Sorted Rows" = Table.Buffer(Table.Sort(Custom1,{{"Index2_", Order.Descending}, {"Index1", Order.Descending}})),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Index2_] <> null)),
MatchesFromSecondTable = Table.Distinct(#"Filtered Rows1", {"Index2_"}),
#"Appended Query" = Table.Combine({MatchesFromFirstTable, MatchesFromSecondTable}),
#"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Index1", "Index2_"})
in
#"Removed Duplicates"