Basic Join Issue

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