Excel Magic Trick 1495 Part 1

Power Query: Allocate Invoice Header Amounts To Transaction Line Item Table


In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level. In this video we use Power Query to take the three tables and convert them using Merges (Joins) and Custom Columns in Power Query into our final report.


1. Introduction & Problem Setup

2. Power Query Solution including multiple Mergers (Joins) and Custom Columns.

3. Duplicate Query

4. F2 key to re-name column

5. Add Custom Column using Add Column, Multiply to calculate Line Sales which will automatically add Table.AddColumn Power Query Function (M Code Function)

6. Use Merge feature (Join feature) to “lookup” Product Weight.

7. Add Custom Column using Add Column, Multiply to calculate Line Weight.

8. Use Group By feature to add Sales and Weight for each Invoice. This is a Power Query feature to aggregate by a condition that is similar to PivotTables or SUMIFS in Excel.

9. Use Merge feature (Join feature) to “lookup” Invoice Discount & Invoice Shipping

10. Add Custom Column using Add Column, Divide to calculate Invoice % Discount.

11. Merge Header Invoice Level Table with Line Item Table.

12. Second Consecutive Merge, now to get Weight from dProduct Table.

13. Add Custom Column using Add Column, Multiply to calculate Line Discount

14. Add Custom Column by manually typing out formula for Line Shipping.

15. Group By to get Final Product Report with correct amounts for Shipping and Discount

16. Load Report to Excel Sheet

17. Summary


Download “Excel Magic Trick 1491-1500” Excel-Magic-Trick-1491-1500.zip – Downloaded 484 times – 10 MB

Be the first to comment

Leave a Reply