MSPTDA 33: Data Modeling for Slowly Changing Dimensions with Power Query & PivotTable

In this video learn about how to deal with a “Slowly Changing Dimensions”, which is the Dimension = Employee and the changing Attribute = Team. Learn how to use Power Query to transform the Dimension Table and Fact Table so that we can show Total Sales by Team and Employee using a Standard PivotTable. Learn how to create automatic steps in Power Query and Custom M Code.
Topics:
1. (00:08) Introduction to Data Setup and Goal of Power Query Data Modeling
2. (01:44) Power Query Transformation for Dimension Table, including UnPivot Other Columns.
3. (03:30) Power Query M Code Formula to pull Team Key into Fact Table based on three conditions / criteria. See the functions Table.AddColumn and Table.SelectRows, and how to Define a Variable for a Custom Function. The Formula does a Three Way Lookup in Power Query.
4. (11:09) Build Standard PivotTable Report, including Loading Power Query Output directly to a Standard PivotTable.
5. (11:57) Summary

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[victorsingam]

Great example of the use of power query in data modelling.

[williamarthur]

I never cease to be amazed at just how useful the table within a table lookup is .

[shoeshines]

I'm loving the Inside Table, Outside Table tricks. M Code = More Power! Very cool video showcasing how M Code makes Power Query so flexible.

[LeilaGharani]

Love the flexibility of Power Query! Thank you for a great explanation teammate Mike :)

[chrism]

Excellent video Mike. Thanks for all you do for the team!

[msantosh]

I love it when you add new video in MSPTDA. Thankx a lot Mike

[ExactProBi]

IT and OT tricks are simply awesome Mike, and the way you explain it is super awesome! thank you!

[ricos]

Fantastic video Mike. I often create a step that "imports" another query and then have to skip back to the original table within my steps. I always start typing my query (Table.SelectRows in this case) and I often make mistakes. Your very simple solution of just typing the table name to return the full table before then editing the formula is so obvious and logical, I'm embarrassed at not having thought of it. You have a fantastic way of showing people, not just how to do things, but the logic behind it. I suppose it comes from years of teaching. I really need to go back through my Excel knowledge and learn the art of teaching to others. I think it would really help me learn too. Inspirational sir, well done.

[Anthony_Lecoq]

I love your MSPDTA video, it always open new perspective.thamks

[katerina]

Thank you so much Mike for the great explanation as always, have a great day Mike 🤗

[ogwalfrancis]

The power of Power Query, Great solution, Thanks Mr. MIke, I'm waiting for the amazing interesting power pivot method.

[shubhampawar]

For me it was difficult ...but the way you do things is amazing ❤️

[alexkim]

Just about yesterday I was thinking how do I assign variables in Power Query. Today I came across your video.

[BillSzysz]

Thanks Mike:-) I love the OTIT technique :-))

[dafeac]

Cool, I was somewhat confused about when to use table.buffer I thought I had to use it in the fsales not the dimension table, great vid, already had to use a similar formula at work, and first learned this tricks here at excelisfun

[НуржанКубесов]

Obviously, instead of using drillthrough ... {0}[Team], type text) in the formula Table.AddColumn (...) of the 3wayLookup query step, we can use EXPAND?

[eziola]

Mike, is there a video where you slowly explain the what the custom functions parameters OT and IT are doing? I'm still confused. It seems like the OT variable is a "Record" while the IT variable is a "Table". Do you have a video link that shows this technique specifically and how it is working? Or can you suggest some reading on the topic?

[richardhay]

No doubt DA not only easier but also shows each employee with or without sales!

[mwilliquette]

Thanks Mike. This is extremely helpful. Question = Is there way way to add additional columns within one step or would you need to repeat the lookup for multiple columns?

[GeertDelmulle]

Super great video, Mike! Thanks!
Again a PQ video where you string a good bunch of gold nuggets together: nested contexts (OT/IT), drill-down, buffered table...
BTW: that buffered table technique seems very efficient, I would have used the merge function. Which one would be the most efficient? Did you test that?