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

SDream7's avatar
SDream7
Helper I
8 years ago

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's avatar
    RahulYadav
    Resolver II
    8 years ago

    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's avatar
      SDream7
      Helper I
      8 years ago

      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.

      • ImkeF's avatar
        ImkeF
        Community Champion
        8 years ago

        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"