Replicate SQL join in Power Query

[Raj12]

I have a simple SQL query that do Inner join between two tables with matching ID's & Lead No but where Date is different in 2 tables.

I want to replicate this in Power Query, tried doing Merege tables in it with Inner Join and then creating calculated column to eclude data with unmatched date but unable to get correct data.

Cleint table needs to be goruped by Lead_Type and then merging of tables needs to be done. Please advise if someone has better method to implement this.

SQL Query

SELECT DISTINCT Client_A
Lead_type_A,

FROM
Cleints

INNER JOIN Leads
ON Cleint_A = Cleint_B
AND Lead_A = Lead_B
AND Date_A <> Date_B

[DataNinja777 · Accepted solution]

Hi @Raj12 ,

You can replicate this SQL query in Power Query by first performing an inner join on the columns that require an exact match and then applying a subsequent filter to handle the "not equal" date condition. The standard merge interface does not support non-equi joins, so this two-step process is the correct and most efficient method. The SELECT DISTINCT part of your SQL query, which ensures unique results, is accomplished in the final step by removing duplicate rows.

To begin, you would use the Merge Queries feature found on the Home tab. In the dialog, select the Clients table and the Leads table. You must then define the join keys by selecting the Cleint_A and Lead_A columns in the first table, and the corresponding Cleint_B and Lead_B columns in the second, ensuring an Inner Join is selected. This creates a new column containing nested tables of the matching rows from the Leads table. You will then expand this new column, but you only need to select the Date_B column for the next step.

After expanding the data, you need to filter for rows where the dates are different. The most straightforward way to do this is by navigating to the Add Column tab and selecting Custom Column. Here, you can create a new column that acts as a boolean flag using the simple formula [Date_A] <> [Date_B]. Once this column is created, you can easily filter it to show only the TRUE values, which effectively applies the Date_A <> Date_B condition from your SQL query.

Finally, to match the SELECT DISTINCT Client_A, Lead_type_A clause, you will clean up the table. Select the Client_A and Lead_type_A columns, then right-click on one of their headers and choose Remove Other Columns. With those same two columns still selected, right-click again and select Remove Duplicates. This leaves you with the exact result set your SQL query produces.

The M code generated by these steps in the Advanced Editor would look like this:

let
    Source_Clients = Clients,
    Source_Leads = Leads,
    MergedTables = Table.NestedJoin(Source_Clients, {"Cleint_A", "Lead_A"}, Source_Leads, {"Cleint_B", "Lead_B"}, "LeadsData", JoinKind.Inner),
    ExpandedDate = Table.ExpandTableColumn(MergedTables, "LeadsData", {"Date_B"}, {"Date_B"}),
    FilteredRows = Table.SelectRows(ExpandedDate, each [Date_A] <> [Date_B]),
    SelectedColumns = Table.SelectColumns(FilteredRows, {"Client_A", "Lead_type_A"}),
    RemovedDuplicates = Table.Distinct(SelectedColumns)
in
    RemovedDuplicates

Best regards,

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on Microsoft Fabric community.

You can do an inner merge on the composite key (client, lead) then expand the merged columns to bring in Date_B, and filter the result to keep only rows where Date_A is different from Date_B and don't forget toremove duplicates and group by Lead_type_A if you want a summary.

= let
  ClientsTyped = Clients,
  LeadsTyped = Leads,
  Joined = Table.NestedJoin(
      ClientsTyped, {"Client_A","Lead_A"},
      LeadsTyped,   {"Client_B","Lead_B"},
      "Leads",
      JoinKind.Inner
  ),
  Expanded = Table.ExpandTableColumn(Joined, "Leads", {"Date_B","Owner"}, {"Date_B","Owner"}),
  DateMismatch = Table.SelectRows(Expanded, each [Date_A] <> [Date_B]),
  DistinctRows = Table.Distinct(DateMismatch, {"Client_A","Lead_type_A","Lead_A","Date_A","Date_B","Owner"})
in
  DistinctRows

you will find an example with data in the pbix file attached.

[DataNinja777 · Accepted solution]

Hi @Raj12 ,

You can replicate this SQL query in Power Query by first performing an inner join on the columns that require an exact match and then applying a subsequent filter to handle the "not equal" date condition. The standard merge interface does not support non-equi joins, so this two-step process is the correct and most efficient method. The SELECT DISTINCT part of your SQL query, which ensures unique results, is accomplished in the final step by removing duplicate rows.

To begin, you would use the Merge Queries feature found on the Home tab. In the dialog, select the Clients table and the Leads table. You must then define the join keys by selecting the Cleint_A and Lead_A columns in the first table, and the corresponding Cleint_B and Lead_B columns in the second, ensuring an Inner Join is selected. This creates a new column containing nested tables of the matching rows from the Leads table. You will then expand this new column, but you only need to select the Date_B column for the next step.

After expanding the data, you need to filter for rows where the dates are different. The most straightforward way to do this is by navigating to the Add Column tab and selecting Custom Column. Here, you can create a new column that acts as a boolean flag using the simple formula [Date_A] <> [Date_B]. Once this column is created, you can easily filter it to show only the TRUE values, which effectively applies the Date_A <> Date_B condition from your SQL query.

Finally, to match the SELECT DISTINCT Client_A, Lead_type_A clause, you will clean up the table. Select the Client_A and Lead_type_A columns, then right-click on one of their headers and choose Remove Other Columns. With those same two columns still selected, right-click again and select Remove Duplicates. This leaves you with the exact result set your SQL query produces.

The M code generated by these steps in the Advanced Editor would look like this:

let
    Source_Clients = Clients,
    Source_Leads = Leads,
    MergedTables = Table.NestedJoin(Source_Clients, {"Cleint_A", "Lead_A"}, Source_Leads, {"Cleint_B", "Lead_B"}, "LeadsData", JoinKind.Inner),
    ExpandedDate = Table.ExpandTableColumn(MergedTables, "LeadsData", {"Date_B"}, {"Date_B"}),
    FilteredRows = Table.SelectRows(ExpandedDate, each [Date_A] <> [Date_B]),
    SelectedColumns = Table.SelectColumns(FilteredRows, {"Client_A", "Lead_type_A"}),
    RemovedDuplicates = Table.Distinct(SelectedColumns)
in
    RemovedDuplicates

Best regards,