Excel Magic Trick 1498

Power Query Power BI: Transform 2 Fact Tables to Star Schema Data Model (Invoice Data)


In this video learn how to Transform Two Fact Tables (Header Invoice and Invoice Line Item Tables) with Dimension Tables into a Proper Star Schema using Power Query inside Power BI Desktop. The Power Query Steps can be used in Excel or Power BI. Learn how to use Power Query to Import non-Start-Schema Model, Transform the tables using Custom Columns & Merges and other steps to then load a Star Schema Model into Power BI Data Model. Video Steps:

  1. (00:06) Introduction including why we need Star Schema
  2. (04:21) Import Two Fact Table Data Model in Power BI Desktop using Power Query
  3. (05:42) Multiply Columns using Multiply feature to calculate “Line Sales”
  4. (06:39) Merge to lookup Product Weight
  5. (07:13) Multiply Columns using Multiply feature to calculate “Line Weight”
  6. (07:30) 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
  7. (09:24) Merge to lookup Invoice Level Shipping and Discount Amounts
  8. (10:40) Divide Columns using Divide feature to calculate “Invoice % Discount”
  9. (12:00) Expand Group By Rows step from earlier in query to get line item detail
  10. (12:55) Add Data Formatting to Expanded Columns
  11. (14:00) Multiply Columns using Multiply feature to calculate “Line Discount”
  12. (14:38) Create Custom Column to calculate “Line Shipping Costs”
  13. (16:14) Edit the previous two columns by editing the Table.AddColumn function and add the Power Query Function Number.Round
  14. (17:13) Remove Columns that are not part of the final single Fact Table.
  15. (18:00) Hide Fact Table we do not need so it is not imported into Data Model, but instead only used in the Power Query Transformation using the “Enable Load” check box option in the list of queries on the left side of the Power Query Window (Right-click option).
  16. (18:23) Close and Apply to Load Star Schema Data Model into the Power BI Desktop Data Model.
  17. (18:31) Create Relationships between Single Line Item Fact table and the three Dimension Tables.
  18. (19:00) Create five Measures: Total Sales, Total Shipping, Total Discount, Shipping as % of Sales, Discount as % of Sales.
  19. (21:01) Hide Columns we don’t need in Report View (Hide in Report View)
  20. (22:06) Create Visualization that contains the Measures and a Slicer from Each Dimension Table
  21. (25:12) Summary


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

Be the first to comment

Leave a Reply