Looking to find all sales that have a return.

[shophoney]

Hi I have a power query below that list all my sales.

I looking to add a column that identifies any sales that have a returned item.

Then I can see every sales with returns.

let
Source = Sql.Database("doff", "DB0000_171202195501"),
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)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered 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"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"note", "NOTE"}, {"description", "DESCRIPTION"}, {"cost", "COST"}, {"unit_ext_best_price", "RETAIL"}, {"sku_no", "SKU#"}, {"qty", "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"}}),
#"Replaced MENU" = Table.ReplaceValue(#"Renamed Columns","BAYD","MENU",Replacer.ReplaceText,{"LOCATION"}),
#"Replaced BAYVIEW" = Table.ReplaceValue(#"Replaced MENU","0000","BAYVIEW",Replacer.ReplaceText,{"LOCATION"}),
#"Replaced AVER" = Table.ReplaceValue(#"Replaced BAYVIEW","YOND","AVER",Replacer.ReplaceText,{"LOCATION"})
in
#"Replaced AVER"

 

[GraH]

You might want to try a self join. That is doing on merge on the same table. Then in the added table filter it on returned items before expanding the table. It requires some M-code. But you can generate it by duplicating the query and doing the steps In the UI.