Forum Discussion

whistlewhileyouwork's avatar
whistlewhileyouwork
Copper Contributor
Mar 04, 2025

Basic Join Issue

I'm doing what I think is a super basic query in Power Query Editor.

 

I have a table of products. I have another table of Customers and Products. I want to get a merged query list showing null values of products customers don't own yet. My research shows this should be a Left Join on the Product column in both tables. When I do this I get no null values, I only get matched values.

Example

 

ProductCustomerOwns

Customer1 | Product 1

Customer1 | Product 2

Customer3 | Product 1

 

ProductTable

Product 1

Product 2

Product 3

Product 4

 

The result I am looking for is this merged query table:

Customer1 | Product 1 | Product 1

Customer1 | Product 2 | Product 2

Customer1 | Product 3 | null

Customer1 | Product 4 | null

etc etc

 

What I'm getting instead is this:

Customer1 | Product 1 | Product 1

Customer1 | Product 2 | Product 2

 

I've tried so many variations (anti-joins, picking the wrong columns, all kinds of stuff) and I cannot get the query to work.

 

Here's what the Merge query looks like:

let

Source = Table.NestedJoin(ProductsByCustomer, {"Product Allocation Group"}, Products, {"Product Allocation Group"}, "Products", JoinKind.LeftOuter),

#"Expanded Products" = Table.ExpandTableColumn(Source, "Products", {"Product Allocation Group"}, {"Product Allocation Group.1"})

in

#"Expanded Products"

BI & Data Analysis
Excel on Mac
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 05, 2025

    Seems the Left Join you've set up is only showing matches because there are no rows for products that customers do not own in your "ProductsByCustomer" table. You can consider to create a complete "combination" of every customer and every product before performing the join to evaluate where products are missing.

     

    1. Create a List of Unique Customers:

    • Select the "Customer" column from the "ProductsCustomerOwns" table.
    • Remove duplicates to get a unique list of customers.
    • Rename this query to "UniqueCustomers."

    2. Create a Cross Join Between Customers and Products:

    • In "UniqueCustomers", add a new column with a constant value of 1.
    • Do the same for the "ProductTable"—add a column with a constant value of 1.
    • Merge these two tables (UniqueCustomers and ProductTable) using an Inner Join on the constant 1 column. This will create a complete list of all customer-product combinations. Rename this query to "CustomerProductCombination."

    3. Left Join with ProductsCustomerOwns:

    • Take the "CustomerProductCombination" query and perform a Left Join with the "ProductsCustomerOwns" table on both the "Customer" and "Product" columns.
    • Expand the "ProductsCustomerOwns" table after the join.

    4. Identify Missing Products:

    • In the expanded column, look for the rows where the values from the "ProductsCustomerOwns" table are null. These rows represent the products that customers don’t own yet.

     

    let
    // Step 1: Get Unique Customers
    UniqueCustomers = Table.Distinct(Table.SelectColumns(ProductsCustomerOwns, {"Customer"})),

    // Step 2: Add constant column to both tables
    CustomersWithKey = Table.AddColumn(UniqueCustomers, "Key", each 1),
    ProductsWithKey = Table.AddColumn(ProductTable, "Key", each 1),

    // Step 3: Create full combination (Cross Join)
    CustomerProductCombination = Table.RemoveColumns(
        Table.Join(CustomersWithKey, "Key", ProductsWithKey, "Key", JoinKind.Inner),
        {"Key"}
    ),

    // Step 4: Left Join with ProductsCustomerOwns
    CombinedQuery = Table.NestedJoin(
        CustomerProductCombination, 
        {"Customer", "Product"}, 
        Products

     

Resources