Hi,
I'm trying to either with POWER QUERY or DAX find a way to go through my list of sold items by invoice number and look for items returned that were never sold to the customer. The item could be sold to another customer which is fine. But I'm looking for returns that are not valid as the customer never purchased them.
Power Query would be better but Dax would be slower.
Any suggestions on how to do it? Iteration column in dax?
This is my POWER QUERY
let
Source = Sql.Database("server", "DB0001_230804114905"),
dbo_tblSale = Source{[Schema="dbo",Item="tblSale"]}[Data],
#"Filtered <1/31/22" = Table.SelectRows(dbo_tblSale, each [date] > #datetime(2022, 1, 31, 0, 0, 0)),
#"Expanded tblCustomer" = Table.ExpandRecordColumn(#"Filtered <1/31/22", "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", {"affect_inv", "affect_total", "cost", "description", "note", "qty", "sku_no", "tblSaleDiscount", "unit_ext_best_price"}, {"affect_inv", "affect_total", "cost", "description", "note.Sale Item", "qty", "sku_no", "tblSaleDiscount", "unit_ext_best_price"}),
#"Expanded tblSaleDiscount" = Table.ExpandTableColumn(#"Expanded tblSaleItem", "tblSaleDiscount", {"disc_code", "amount"}, {"disc_code", "Discount Amount"}),
#"Filtered TRANS ONLY" = Table.SelectRows(#"Expanded tblSaleDiscount", each [trans_type] <> "0CLO" and [trans_type] <> "0CTP" and [trans_type] <> "0FLO" and [trans_type] <> "0LSS" and [trans_type] <> "0PIC" and [trans_type] <> "0COU"),
#"Grouped Rows" = Table.Group(#"Filtered TRANS ONLY", {"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, note.Sale Item]}}),
#"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", "tblVoid", "note.Sale Item"}, {"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", "tblVoid", "note.Sale Item"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ALL ROWS",{ {"date", type date}}),
#"Inserted CUSTOMER" = Table.AddColumn(#"Changed Type", "CUSTOMER", each Text.Combine({[Customer Last Name], [Customer First Name]}, ", "), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted CUSTOMER",{ "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#"}, {"Discount Amount", "DISCOUNT AMOUNT"}, {"disc_code", "DISC CODE"}, {"Employee", "EMPLOYEE"}, {"date", "DATE"}, {"loc_code", "LOCATION"}, {"trans_no", "TRANS#"}, {"trans_type", "TRANS TYPE"}}),
#"Merged NOTES" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns", {{"note.Sale Item", type text}}, "en-US"),{"NOTE", "note.Sale Item"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"NOTES"),
#"Capitalized Each Word" = Table.TransformColumns(#"Merged NOTES",{{"DESCRIPTION", Text.Proper, type text}, {"CUSTOMER", Text.Proper, type text}, {"NOTES", Text.Proper, type text}})
in
#"Capitalized Each Word"
I'm trying to either with POWER QUERY or DAX find a way to go through my list of sold items by invoice number and look for items returned that were never sold to the customer. The item could be sold to another customer which is fine. But I'm looking for returns that are not valid as the customer never purchased them.
Power Query would be better but Dax would be slower.
Any suggestions on how to do it? Iteration column in dax?
This is my POWER QUERY
let
Source = Sql.Database("server", "DB0001_230804114905"),
dbo_tblSale = Source{[Schema="dbo",Item="tblSale"]}[Data],
#"Filtered <1/31/22" = Table.SelectRows(dbo_tblSale, each [date] > #datetime(2022, 1, 31, 0, 0, 0)),
#"Expanded tblCustomer" = Table.ExpandRecordColumn(#"Filtered <1/31/22", "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", {"affect_inv", "affect_total", "cost", "description", "note", "qty", "sku_no", "tblSaleDiscount", "unit_ext_best_price"}, {"affect_inv", "affect_total", "cost", "description", "note.Sale Item", "qty", "sku_no", "tblSaleDiscount", "unit_ext_best_price"}),
#"Expanded tblSaleDiscount" = Table.ExpandTableColumn(#"Expanded tblSaleItem", "tblSaleDiscount", {"disc_code", "amount"}, {"disc_code", "Discount Amount"}),
#"Filtered TRANS ONLY" = Table.SelectRows(#"Expanded tblSaleDiscount", each [trans_type] <> "0CLO" and [trans_type] <> "0CTP" and [trans_type] <> "0FLO" and [trans_type] <> "0LSS" and [trans_type] <> "0PIC" and [trans_type] <> "0COU"),
#"Grouped Rows" = Table.Group(#"Filtered TRANS ONLY", {"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, note.Sale Item]}}),
#"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", "tblVoid", "note.Sale Item"}, {"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", "tblVoid", "note.Sale Item"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ALL ROWS",{ {"date", type date}}),
#"Inserted CUSTOMER" = Table.AddColumn(#"Changed Type", "CUSTOMER", each Text.Combine({[Customer Last Name], [Customer First Name]}, ", "), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted CUSTOMER",{ "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#"}, {"Discount Amount", "DISCOUNT AMOUNT"}, {"disc_code", "DISC CODE"}, {"Employee", "EMPLOYEE"}, {"date", "DATE"}, {"loc_code", "LOCATION"}, {"trans_no", "TRANS#"}, {"trans_type", "TRANS TYPE"}}),
#"Merged NOTES" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns", {{"note.Sale Item", type text}}, "en-US"),{"NOTE", "note.Sale Item"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"NOTES"),
#"Capitalized Each Word" = Table.TransformColumns(#"Merged NOTES",{{"DESCRIPTION", Text.Proper, type text}, {"CUSTOMER", Text.Proper, type text}, {"NOTES", Text.Proper, type text}})
in
#"Capitalized Each Word"
