Forum Discussion

SDream7's avatar
SDream7
Helper I
8 years ago

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], [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,
Need Help

12 Replies

  • RahulYadav's avatar
    RahulYadav
    Resolver II
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      RahulYadav

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

      However, I get below error -

       

       

       

       

       

       

       

       

       

      Any idea?

      • RahulYadav's avatar
        RahulYadav
        Resolver II
        8 years ago

        Hi SDream7,

        Sorry for the confusion.

        I have added join based on ClientID field.

         

        Thanks,

        Rahul

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    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

  • Sathindrajith's avatar
    Sathindrajith
    New Member
    3 years ago

    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>)