Power Query Power BI: Transform 2 Fact Tables to Star Schema Data Model (Invoice Data) EMT 1498
Вставка
- Опубліковано 1 жов 2024
- Download File: Source Data Tables : people.highlin... Finished Power BI File: people.highlin...
Entire page with all Excel Files for All Videos: people.highline...
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 - Explain in Dept thanks for this explanation sir.
You are welcome, Teammate!
Thank you for the Power Query fun!
You are welcome, Teammate! : )
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!
That is why i have posted over 3000 videos over for over 10 years : ) So people in the world can use Excel to have fun, be efficient and helps others to do cool stuff with excel!!!! AND, thank you, Yashvardhan, for so consistently supporting this channel : ) Every comment you leave is really great : )
ExcelIsFun You, sir, deserve the very best. Thank you so much! It is the person's pleasant qualities that encourage others to compliment and thank them. You definitely have those qualitites.
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!
Power Query really can make things simple! You are welcome for this : )
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.
Yes, I am happy to teach the concepts and tricks. That is what i have been doing for over 10 years here at UA-cam : ) I am glad that you love it - because I love to make these videos. You can help support this free resource that I post by commenting when you are inspired, clicking Thumbs Up on each video, Subscribing and tell all your friends : ) BTW, if you like this video, you will LOVE the new class i am posting over the next nine months: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thanks for your support, Nattawut : )
Thanks a lot, Mike. I'm a big fan of you and have followed your channel more than 3 years. I have learnt tons of techniques. I can feel that you love to make these videos and it makes all video lively. Your noise, your emotion, mouse movement can express that. You are my great teacher.
Big thanks from Thailand.
P.S. I will follow the MSPTDA series for sure
I am glad it all helps! And, yes, if you love what you do - good things will happen : ) And be sure to help and support with those Thumbs Up and comments : ) Thanks, Nattawut!!!
Thanks! Reviewed this again, and it’ll help me allocate and round properly in work project. Have a coffee or beer on me.
Thanks for the coffee and beer, Milhouse!!!!
Never seem to have enough of this video and practice!!!! This is just Amazing.
Man! This is intense! Thanks for helping me see what a star schema is.
Intense is good : ) You are welcome, Oz!!!
A for apple... E for ExcelIsMike... M for MikeIsExcel... Thats the foundation line of this channel
AEM!!!!
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 .
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).
you can use constellation schema if you have multiple fact tables
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.
You are welcome!
I can't describe your smartness in excel anymore you are beyond perfect
Just a guy having fun with Excel and trying to spread the fun around the world : ) Thanks for your consistent support, Ismail!!
Thank you Mike for Power Query video. I'm very interested in Power Query technique to solve problem.
You are welcome, Jung!!! Thanks you for the support with your comments, Thumbs Ups and Sub to help me keep going : ) P.S. Many more Power Query videos to come over the next year. Also, do you know about the over 100 Power Query videos that I have already posted over the last 3 years? Here is the playlist: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html
Thanks Mike for this EXCELlent video. Always perfect
Thanks, Syed!!! Thanks as always for your support : )
Fantastic video, Mike! Thank you! Question on this subject: what do I do in a situation when the intermediate table (one side of the relationship - say, fPatientVisits) has more records (PK) records than the same (FK) records the child table has fPatientVisitProcedures (many side of the relationship on PatientVisitID). In other words, not every Visit has a Procedure. If I join based on PK from fPatientVisits, will I have to do a LEFT JOIN on fPatientVisits and generate additional empty values in the resulting fPatientVisitProcedurestable? Basically, I don't want to count my number of unique visits incorrectly in the resulting table only because not every visit had a procedure. Many thanks!
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! :-)
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.
Got it! Thanks for the tip!
That's what I usually do, too. Not going to lie though, really liking the idea of putting the measures in the fact table and hiding the other values. More effort, but probably an easier experience for the end user.
Superb Video! This helped me to solve the problem I had been struggling with for days. Thank you so much! I learned so much
Amazing Video Sir, suggest you should create Key/Core Measure Table separately for measures only
why dont you remove weight field from dproduct table.. because this is already calculated in fact table ? and in dimension we just want fields not any numeric data ?
Simply amazing MIke!!!!
Glad you like it, Chris! Thanks for the support : )
Thanks Mike :) :)
You are welcome, John!!! Now that you have downloaded Power BI Desktop, you have even more Microsoft power and ability to have fun : )
Star Schema waw very fun Excel makes life easy
How do we handle the case of changing dimensions? For example, employee sales performance as fact, and employee as dimension. Previously, the employee is assigned to sales team A. Now, the employee is assigned to sales team B. If we want to analyze performance for various teams over time, how should the schema be built to accommodate this kind of situations?
I made 5 videos about your question. Did you see them? I never saw your comments below the videos I posted, so I was not sure if you knew that I made videos for you. You should have seen the announcements if you were subscribed. Are you subbed? Here is the playlist of videos: ua-cam.com/play/PLrRPvpgDmw0nvWXu8EwGQnfoJv7Ca2st3.html
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.
Yes, as time goes on and I use Power Query more and more, Power Query is the tool that makes everything easier!! Thanks for stopping by in the comments, Joad Lee : )
Is there ever an instance where you would use multiple fact tables? For example: would you combine Sales, Product Costs, and Payroll fact tables? What if there were several fields in the payroll table that aren't relevant to sales records?
This is a very good question! I hope someone will answer it ... sometime
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.
Yes, it is amazing what Microsoft has done over the last 10 years with dynamic Excel Tables, Power Query and Power Pivot so that we can do soooooo much more in Excel. We can even store 100 milliow rows in Excel's Power Pivot Data Model : ) You are welcome for the videos, Sara!!!
Thanks so much for you video. Pls my question may not relate to the current video and i am sorry for this. i want to ask if you have any video using power query for approximate match for dates.
I do not have a video on that... Maybe Oz video: ua-cam.com/video/EYgKciBr_dg/v-deo.html
Thanks for Power BI fun :)
Hi Mike. I am trying to create a MonthName column from MonthNumber using Format([MonthNumber],"MMM"). The problem is, it is converting the number 1 to January and remaining every number (2 to 12) to December or number 1 to December and remaining month numbers to January. I don't know if there is BUG or my version is not working properly. Can you please help me with this problem? Help is much appriciated.
You have to do it on the [Date] column, not the [MonthNumberColumn].
It's the same issue even I use the Date Column. I tried but it is still the same.
Thanks you! This was fireworks! :)
Glad you like it, Mueez!!!
Fantastic video Mike. I have learned a great deal from your tutorials of late. Any thoughts of a video to show how to manage the numerous data source files (i.e exports from accounting systems, folder location mgmt to Access db's etc..?) Managing these multiple source connections efficiently takes some thought and organization. Keep up the great work...Thanks
I have videos on importing from folders, Access, Excel, SQL database and multiple locations. Here is a playlist of Power Query Videos: ua-cam.com/play/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK.html This video playlist has over 100 videos, but most of the topics are covered in a video in this list. However, I do not have a single video that goes over thought and organization of all the connections for one final model and reporting system. What are some of the issues that you face?
Thanks for your support with your comments, Thumbs Up and Sub, Matt : )
Thanks Mike. I will be sure to go through the playlist. My thoughts are geared towards "Best Practices" for organizing the exported files from my accounting software into a folder, then running them through power query to import them into my Access db. With more of your awesome content, I am sure I will learn the proper way. Keep up the fantastic work!!
As always, great video. Do you also have other channels for teaching other office products? Thanks.
No. Sorry. But I do have a playlist with some basic office tools: ua-cam.com/play/PLrRPvpgDmw0l45snFj8uDmuDUMQeBAVtd.html
Thanks for your support with your comments, Thumbs Ups and Sub : )
Thanks for the reply. And I'm a subscriber already. How could I not be? Great Channel.
I have a question. On the total shipping field. You did a divide and then after you multiplied. I usually put my divide in parentheses. So in M language it doesn’t matter it will know to apply the divider before the multiplier?
It does not matter what system in the history of the world that you use, Math Order of Operations rules supreme!!!! Since, left to right, we divide first to get % allocation rate, then multiple, you do not need the Parenthesis in Excel, Power Query, DAX or whatever : )
Thanks for your support, Karl : )
ExcelIsFun good to know one less step to do :)
Amazing, learnt great new things
Glad the videos help : )
This is great!! It seems, I couldn't find referred excel data file in comments to test it.
Yes, I am sorry about that. I have edited the description so that it is there. I just wish someone had mention this sooner. Thanks, kumail, for your support of the excelisfun channel : )
Thanks.
Thank You.....
:)
You are welcome!