Power Query Power BI - Transform 2 Fact Tables to Star Schema Data Model - Invoice Data

Learn how to transform two fact tables (Header Invoice and Invoice Line Item) with dimension tables into a proper Star Schema using Power Query in Power BI Desktop. Master techniques for importing non-Star-Schema models, transforming tables with custom columns and merges, and loading a Star Schema model into Power BI Data Model. Explore solutions for header-detail granularity reporting issues, invoice level mismatches, and allocating invoice totals to detail levels. Follow step-by-step instructions to create measures, establish relationships, and build visualizations that leverage the newly created Star Schema data model.

Syllabus

) Introduction including why we need Star Schema.
) Import Two Fact Table Data Model in Power BI Desktop using Power Query.
) Multiply Columns using Multiply feature to calculate “Line Sales”.
) Merge to lookup Product Weight.
) Multiply Columns using Multiply feature to calculate “Line Weight”.
) 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.
) Merge to lookup Invoice Level Shipping and Discount Amounts.
) Divide Columns using Divide feature to calculate “Invoice % Discount”.
) Expand Group By Rows step from earlier in query to get line item detail.
) Add Data Formatting to Expanded Columns.
) Multiply Columns using Multiply feature to calculate “Line Discount”.
) Create Custom Column to calculate “Line Shipping Costs”.
) Edit the previous two columns by editing the Table.AddColumn function and add the Power Query Function Number.Round.
) Remove Columns that are not part of the final single Fact Table..
) 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)..
) Close and Apply to Load Star Schema Data Model into the Power BI Desktop Data Model..
) Create Relationships between Single Line Item Fact table and the three Dimension Tables..
) Create five Measures: Total Sales, Total Shipping, Total Discount, Shipping as % of Sales, Discount as % of Sales..
) Hide Columns we don’t need in Report View (Hide in Report View).
) Create Visualization that contains the Measures and a Slicer from Each Dimension Table.
) Summary.