Power Query looking for item not in list

[shophoney]

Hi,

I'm looking to find items with returns (-1) units that were never purchased.

Below is my power query that pulls all sales and returns. But I want to create a column that says the item returned (-1) was never purchased. Therefore does not exists in the table as a value of greater than 0.

But this is based on customer transactions. So if the customer never purchased the item. IE some other customer might have purchased it but you didn't so how can you return this item.

let
    Source = Sql.Database("server", "DB0001_230804114905"),
    dbo_tblSale = Source{[Schema="dbo",Item="tblSale"]}[Data],
    #"Expanded tblCustomer" = Table.ExpandRecordColumn(dbo_tblSale, "tblCustomer", {"name", "last_name"}, {"Customer First Name", "Customer Last Name"}),
    #"Expanded tblEmployee" = Table.ExpandRecordColumn(#"Expanded tblCustomer", "tblEmployee", {"name"}, {"Employee"}),
    #"Expanded tblSaleItem" = Table.ExpandTableColumn(#"Expanded tblEmployee", "tblSaleItem", {"sku_no", "affect_inv", "affect_total", "unit_ext_best_price", "cost", "qty", "description", "tblSaleDiscount"}, {"sku_no", "affect_inv", "affect_total", "unit_ext_best_price", "cost", "qty", "description", "tblSaleDiscount"}),
    #"Expanded tblSaleDiscount" = Table.ExpandTableColumn(#"Expanded tblSaleItem", "tblSaleDiscount", {"disc_code", "amount"}, {"disc_code", "Discount Amount"}),
    #"Expanded tblSaleTender" = Table.ExpandTableColumn(#"Expanded tblSaleDiscount", "tblSaleTender", {"tender_code", "amount"}, {"tender_code", "amount"}),
    #"Filtered TRANS ONLY" = Table.SelectRows(#"Expanded tblSaleTender", each [trans_type] <> "0CLO" and [trans_type] <> "0CTP" and [trans_type] <> "0FLO" and [trans_type] <> "0LSS" and [trans_type] <> "0PIC" and [trans_type] <> "0COU"),
    #"Filtered Rows" = Table.SelectRows(#"Filtered TRANS ONLY", each [date] > #datetime(2022, 1, 31, 0, 0, 0)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"sale_link"}, {{"MIN UNITS", each List.Min([qty]), type nullable number}, {"ALL ROWS", each _, type table [sale_link=text, source_loc=text, loc_code=text, register_code=text, trans_no=number, register_type=text, link_journal=text, link_order=nullable text, date=datetime, time=datetime, trans_type=text, cashier=text, customer_code=nullable text, voided_by=nullable number, void_code=nullable text, suspended=nullable text, gr_no=nullable text, status=nullable text, sale_type_curr=text, dumped=logical, note=nullable text, Customer First Name=nullable text, Customer Last Name=nullable text, Employee=nullable text, tblGREntry=nullable record, tblJournal=nullable record, tblLocation=nullable record, tblOrder=nullable record, tblRegister=nullable record, sku_no=nullable number, affect_inv=nullable logical, affect_total=nullable logical, unit_ext_best_price=nullable number, cost=nullable number, qty=nullable number, description=nullable text, disc_code=nullable text, Discount Amount=nullable number, tblSaleRewards=table, tblSaleShipTo=table, tblSaleTax=table, tender_code=nullable text, amount=nullable number, tblVoid=nullable record]}}),
    #"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "RETURN", each if [MIN UNITS] <= 0 then "RETURN" else "PURCHASE"),
    #"Expanded ALL ROWS" = Table.ExpandTableColumn(#"Added Conditional Column", "ALL ROWS", {"source_loc", "loc_code", "register_code", "trans_no", "register_type", "link_journal", "link_order", "date", "time", "trans_type", "cashier", "customer_code", "voided_by", "void_code", "suspended", "gr_no", "status", "sale_type_curr", "dumped", "note", "Customer First Name", "Customer Last Name", "Employee", "tblGREntry", "tblJournal", "tblLocation", "tblOrder", "tblRegister", "sku_no", "affect_inv", "affect_total", "unit_ext_best_price", "cost", "qty", "description", "disc_code", "Discount Amount", "tblSaleRewards", "tblSaleShipTo", "tblSaleTax", "tender_code", "amount", "tblVoid"}, {"source_loc", "loc_code", "register_code", "trans_no", "register_type", "link_journal", "link_order", "date", "time", "trans_type", "cashier", "customer_code", "voided_by", "void_code", "suspended", "gr_no", "status", "sale_type_curr", "dumped", "note", "Customer First Name", "Customer Last Name", "Employee", "tblGREntry", "tblJournal", "tblLocation", "tblOrder", "tblRegister", "sku_no", "affect_inv", "affect_total", "unit_ext_best_price", "cost", "qty.1", "description", "disc_code", "Discount Amount", "tblSaleRewards", "tblSaleShipTo", "tblSaleTax", "tender_code", "amount", "tblVoid"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded ALL ROWS",{ {"date", type date}, {"amount", Currency.Type}}),
    #"Inserted CUSTOMER" = Table.AddColumn(#"Changed Type", "CUSTOMER", each Text.Combine({[Customer Last Name], [Customer First Name]}, ", "), type text),
    #"Inserted TRANSACTION HEADER" = Table.AddColumn(#"Inserted CUSTOMER", "TRANSACTION HEADER",  each Text.Combine({Text.From([trans_no], "en-US"), Text.From([date], "en-US"), [trans_type], [Employee], [CUSTOMER], [tender_code], Text.From([amount], "en-US")}, ", "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted TRANSACTION HEADER",{"sale_link", "source_loc", "register_code", "register_type", "link_journal", "link_order", "time", "cashier", "gr_no", "sale_type_curr", "dumped", "Customer First Name", "Customer Last Name", "tblGREntry", "tblJournal", "tblLocation", "tblOrder", "tblRegister", "tblSaleRewards", "tblSaleShipTo", "tblSaleTax", "tblVoid", "MIN UNITS"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"note", "NOTE"}, {"description", "DESCRIPTION"}, {"cost", "COST"}, {"unit_ext_best_price", "RETAIL"}, {"sku_no", "SKU#"}, {"qty.1", "UNIT"}, {"amount", "TENDER AMOUNT"}, {"Discount Amount", "DISCOUNT AMOUNT"}, {"disc_code", "DISC CODE"}, {"Employee", "EMPLOYEE"}, {"date", "DATE"}, {"loc_code", "LOCATION"}, {"trans_no", "TRANS#"}, {"trans_type", "TRANS TYPE"}, {"tender_code", "TENDER"}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Renamed Columns",{{"DESCRIPTION", Text.Proper, type text}, {"CUSTOMER", Text.Proper, type text}})
in
    #"Capitalized Each Word"

 

[Matt Allington]

I’m on an iPad, so can’t look at your query. I would do the following
1 create a staging query with all data
2 reference that Query and create a table with all purchases. Exclude returns
3 do the same again, but all returns, exclude purchases
4 join 2 with 3 looking for product/cust combinations in 3 that don’t exist in 2