The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have two tables, Orders and Invoices. The Invoice table has both Order Number and Invoice Number.
I'm trying to get the Sum of Sales from the Invoice Table + the sum of sales not yet invoiced using the order number column.
I'm also trying to get the Sales not yet invoiced using the same column filters.
This is what I been fiddling with currently.
Solved! Go to Solution.
Hi,
This M code works
let Source = Table.Combine({Invoices, Orders}), Partition = Table.Group(Source, {"Sales Order"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Invoice Order", "Sales", "Index"}, {"Invoice Order", "Sales", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Index] = 1)), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Invoice Order", "Sales Order", "Sales", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Invoice Order", "Invoice Number"}, {"Sales", "Sales + outstanding"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales + outstanding", type number}}) in #"Changed Type"
Hope this helps.
Hi @Tuan ,
I'd like some sample data with expected result to clarify your data structure and do test on it.
Regards,
Xiaoxin Sheng
When Orders convert to Invoices the values can change. I'm trying to get the "Sales + Outstanding" value using a measure.
Here's the tables. I also put the Model, I made an inactive many-to-many between the invoice and order to try to do what I need.
Hi,
This M code works
let Source = Table.Combine({Invoices, Orders}), Partition = Table.Group(Source, {"Sales Order"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Invoice Order", "Sales", "Index"}, {"Invoice Order", "Sales", "Index"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Index] = 1)), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Invoice Order", "Sales Order", "Sales", "Index"}), #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Invoice Order", "Invoice Number"}, {"Sales", "Sales + outstanding"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales + outstanding", type number}}) in #"Changed Type"
Hope this helps.
That works and I did something similiar, trying to figure out a a dax solution instead of flattening the tables.