Merging queries based on multiple conditions

[SDream7]

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], [Client ID], [Personal ID], [Date of Birth] and a concatenated field that's derived from combining both [First Name] & [Date of Birth] together, which I'll refer to as [Concatenated First/DOB].

Table B also has a variety of different fields AND also includes the fields of interest I listed for Table A.

Both tables essentially contain their own set of clients, who may or may not appear on both tables.

Using the Merge Query function, I'm only able to merge both tables based on a single matching column. For my purposes, I want my rows on both tables to merge if ANY of the following holds true:

TableA.[Client ID] matches TableB.[Client ID]

TableA.[Personal ID] matches TableB.[Personal ID]

TableA.[Concatenated First/DOB] matches TableB.[Concatenated First/DOB]

I'm new to Power BI and my assumption is that I'd need to add some combination of IF / OR statements on the Advanced Editor, but i'm not sure how to do this with M query language.

Here's what the M statement looks like on Advanced Editor when I merge both tables based on a single matching column:

= Table.NestedJoin(#"Added Custom", {"TableA.Client ID”}, #"XLS Export File (3)",{"TableB.Client ID" },"XLS Export File (3)",JoinKind.FullOuter

What additions would I have to add to my M statement in order to merge the two tables based on the criteria I listed above?

Also, I still want to keep all non-matching TableA Clients and TableB Clients on this newly merged table even though I know they would have all nulls on either the Table A Fields or Table B Fields. Am I correct in using the full outer option at the end of my M statement?

Thank you,

[Sathindrajith]

You can use the following Mquery to add specific columns and the conditions u wanna join.

Table.NestedJoin(<Previous step in the Mquery>, {"Client ID", "Personal ID","Concatenated First/DOB"}, TableB, {"Client ID", "Personal ID","Concatenated First/DOB"}, <New Table Name>, <Type of Join you require>)

[RahulYadav]

Hi @SDream7,

Please try following below steps for achieving this using dax.

1. The join fields should have different names in TableA & TableB.

2. Import both tables to Power BI.

3. Do not add joins on the tables.

4. Add a New Table using below DAX Formula.

TableC = DISTINCT( union(
    FILTER(CROSSJOIN(TableA,TableB),TableA[Client ID]=TableB[ClientID]),
    FILTER(CROSSJOIN(TableA,TableB),TableA[Personal ID]=TableB[PersonalID]),
    FILTER(CROSSJOIN(TableA,TableB),TableA[Concatenated First/DOB]=TableB[ConcatenatedFirst/DOB]),
    NATURALLEFTOUTERJOIN(TableA,TableB),
    NATURALLEFTOUTERJOIN(TableB,TableA)))

This will give you the details you are looking for. Sample Belw:

TableA:

TableB:

TableC:

Thanks,

Rahul

[Anonymous]

@RahulYadav

As per your point#3, you have said not to add any joins between tables.

However, I get below error -

Any idea?

[RahulYadav]

Hi @SDream7,

Sorry for the confusion.

I have added join based on ClientID field.

Thanks,

Rahul

[SDream7]

I got the same issue as the user above:

"No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column."

How would I fix this?  What do you mean you added join based on Client ID?

Sorry, i'm new to BI 😞

[RahulYadav]

Hi @SDream7,

Create a new join between TableA & TableB as below through Manage Relationship.

 

Let me know if you get the desired result.

Thanks,

Rahul

[SDream7]

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.

[RahulYadav]

Hi @SDream7,

I think you are using same name "Client ID" in both tables. For NATURALLEFTOUTERJOIN to work properly, the "Client ID" field name should be different in both tables TableA & TableB. 

Could you please share the screenshot of DAX expression you used for getting TableC?

Thanks,

Rahul

[ImkeF]

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"

[SDream7]

I copied the code and created the function but I keep getting this error:

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "Client ID" to type List.
Details:
    Value=Client ID
    Type=Type

[ImkeF]

Did you use the curly brackets around your list of column names?

[MFelix]

Hi @SDream7,

This is a tricky one believe that @ImkeF can help you, she is an M expert and always shows new ways to change our data she is a great Datanaut.

Regards,

MFelix