Download File: Source Data Tables : people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1498-SourceModel.xlsx Finished Power BI File: people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT-1498-PowerBIDesktopPowerQuery.pbix
Entire page with all Excel Files for All Videos: people.highline.edu/mgirvin/excelisfun.htm
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
Search terms in this video: Header Detail Granularity Reporting Problem, Invoice Level, Invoice Detail Level Mismatch, Invoice Granularity Mismatch Reporting Issue, Invoice Total / Invoice Detail, Allocating Invoice Totals to Invoice Detail Level, Header/Line Item Transactions, Header / Line Item Transactions Reporting Issues, Two Fact Tables, Different Granularity, Two Transaction Tables, Different Granularity, Star Schema, Transform Two Fact Tables to Star Schema
Excel Magic Trick 1498
- Power Query Power BI: Transform 2 Fact Tables to Star Schema Data Model (Invoice Data) EMT 1498 ( Download)
- Handling MULTIPLE fact tables in Power BI ( Download)
- How to setup a Star Schema Data Model in Power BI - Easy guide ( Download)
- Power BI – Multiple Fact Tables ( Download)
- Data Modeling (Star Schema 🌟) in Power BI – Creating Dimension Tables ( Download)
- Fact & Dimension tables in Power BI | Data Modelling ( Download)
- The Dimensional Dilemma and Power BI ( Download)
- How to Merge Fact and Dimension Table in Power Query ( Download)
- Power BI Advanced Tutorial: Explaining the Star Schema Data Model with Example | DIM and Fact Table ( Download)
- Join Two Fact Tables using a common dimension table Power BI ( Download)
- Solution for Two Fact Tables: Power Query or DAX formulas or Worksheet Formulas 365 MECS Class 18 ( Download)
- Number of Fact tables in a data model Power BI ( Download)
- Power BI star schemas and best practice data modelling ( Download)
- Part 1 - Data Modeling with Power BI and Power Query ( Download)
- fact tables in power bi | how to create star schema in power project | ssu ( Download)
