Excel Magic Trick 1495 Part 2

Power Query with Group By Rows: Allocate Invoice Amounts To 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. This video is different than Part 01 because we use Group By Rows to avoid two merges. This trick comes from Bill Szysz. Topics in this video:

1. (00:06) Introduction

2. (01:47) Compare Steps from first video to the steps we do this this video

3. (03:00) Start Query From Excel

4. (04:09) Duplicate Import Source Data

5. (04:40) Multiply Columns using Multiply feature to calculate “Line Sales”

6. (05:51) Merge to lookup Product Weight

7. (06:57) Multiply Columns using Multiply feature to calculate “Line Weight”

8. (07:14) Group By to aggregate Line Sales and Line Weight in order to get Invoice / Header Level Amounts, but we also Group By Rows to save the Line Item Level data so we can use it later in the query

9. (08:59) Merge to lookup Invoice Level Shipping and Discount Amounts

10. (10:31) Divide Columns using Divide feature to calculate “Invoice % Discount”

11. (11:31) Expand Group By Rows step from earlier in query to get line item detail

12. (12:30) Multiply Columns using Multiply feature to calculate “Line Discount”

13. (12:54) Create Custom Column to calculate “Line Shipping Costs”

14. (13:37) Edit the previous two columns by editing the Table.AddColumn function and add the Power Query Function Number.Round

15. (15:00) Group By to get Final Product Report

16. (16:50) 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