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

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

[Whyvardhann]

I would like to tell you that I am one of the viewers of your Excel Basics Series. I have just recently completed the series and feel that they are simply amazing. Yesterday my dad was working on some excel files and I saw that he was working way too hard for the purpose! I got him exactly what he wanted through pivot tables. Your videos have helped me a lot. He was so happy to see how much simpler his work had gotten and also gave me a few other excel sheets to fix! Thank you so much, Mike. I can't thank you enough. I jist can't. Thank you so so much! I hope you get the best in life!

[MilhouseBS]

Thanks! Reviewed this again, and it’ll help me allocate and round properly in work project. Have a coffee or beer on me.

[nattawut_chatwiriyacharoen]

This is a great great video. It's not only illustrate how to use Power Query but also how to build an effective data model in Power BI. There are lots of tips and tricks. Other youtuber will show only how to use commands, but you make people thoroughly understand concept(s) and commands. I love it very much.
Many thanks, Mike.

[johnborg]

Never seem to have enough of this video and practice!!!! This is just Amazing.

[Nura]

Thank you Sir for the great video! I was trying to denormalize 2 fact tables using SQL, but your approach is much simpler and effective!

[Learn_More_Pro]

POWER QUERY - Explain in Dept thanks for this explanation sir.

[timmybone]

It's taking me a while to wrap my head around Star Schema data models and which fields should be in the fact table versus dimension tables, but I'm getting there. Thanks for another great video.

[ismailismaili]

I can't describe your smartness in excel anymore you are beyond perfect

[GeertDelmulle]

This transform to the Star Schema Data Model is the max!
Perfect exercise to define and split the use of PQ vs. DAX. This is my new preferred method.
As you can see I need to re-baseline once again (although I would probably stay in Excel/PP, for now).
If this method were included in the competition -- it would get my single vote.
This mini series on how to solve this problem in various ways keeps on giving, and our understanding of relational data bases keeps on growing -- as ordinary Excel users we need this kind of exercises in order to expand from Excel into this new field.
Thank you Master Mike for your Due Diligence and perseverance!
We collectively salute you! :-)

[OzduSoleilDATA]

Man! This is intense! Thanks for helping me see what a star schema is.

[rrrprogram]

A for apple... E for ExcelIsMike... M for MikeIsExcel... Thats the foundation line of this channel

[villiageheart]

I've always used Access to build databases, but with this power table addition I could try and build a Employee database. Thank you Sir Mike.

[tanushreebubna]

Superb Video! This helped me to solve the problem I had been struggling with for days. Thank you so much! I learned so much

[mohammedfarooq]

Dear, thank for you by size sky, these video very great work, your channel help me mor to develop my work .I wish u happy life .

[joad_lee]

Mike, Power Query is most helpful when it can make Power Pivot easier, which is why this video is the best practice among solutions using Power tools in the playlist. But PivotTable is the easiest solution of all.

[SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video. Always perfect

[Jung-samanhudisound]

Thank you Mike for Power Query video. I'm very interested in Power Query technique to solve problem.

[rislamov]

That’s awesome! As usual.
You’ve asked how to hide fields in report view. I usually create a separate blank table only with measures so I can easy hide whole fact table.

[CJamesEnglish]

Thank you Mike, the "Group By" strategy is something I'll have to try (I was trying pivoting, removing duplicates, and Cartesian Joins and it wasn't working for what I was working on... I think my way might work but it's too messy).

[jitendratrivedi]

Amazing Video Sir, suggest you should create Key/Core Measure Table separately for measures only