Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More!

Excel Data Analysis 02 files:
This is a comprehensive and complete lesson in how to use Power Pivot & DAX Formulas to create reports and visualizations in Excel. It teaches the full process of building a Data Model, Building DAX formulas that you can use for dashboard reporting. This video teaches about the concepts that make Power Pivot work like star schema data modeling, filter context and context transition. If you want the how and whys of Power Pivot & DAX formulas, this is the right video for you.
Topics:
1. (00:00) Intro and overview of Standard PivotTable and Data Model Pivot Table
2. (03:06) 5 Steps in the Data Analysis Process
3. (03:38) Step #1: Get Data using Power Query.
4. (06:12) Show Power Pivot Ribbon Tab.
5. (06:56) Power Query to import text “.CSV” files from a folder, including explanation of Applied Steps and automatic queries created.
6. (11:34) Loading data to the Data Model with Import Data dialog box and using the “Only Create Connection” and the “Add this data to the Data Model”.
7. (12:56) Look at data in Data Model. Preview of Power Pivot for Excel window
8. (13:20) What is a Columnar Data? See how Data is compressed to a small size so that the Data Model can handle Big Data.
9. (15:16) Import Excel Tables to the Data Model using Power Query.
10. (15:29) Decimal Power Query Type is the most consistent Power Query Data Type for numbers with decimals, if you want accurate calculations. If the numbers are whole numbers, use Whole Number Power Query Data Type.
11. (17:01) Step #2: Build Star Schema Data Model.
12. (17:17) Create a Power Pivot Date Table.
13. (19:29) Create One-To-Many Relationships between Fact Table and Dimension Tables.
14. (21:48) Create DAX Calculated Column in Date Table for EOMonth.
15. (24:17) First look at ROW Context to make a calculation in each row in a DAX Calculated Column (or DAX Iterator).
16. (26:05) Create Total Sales Measure with 2 Step Process: 1) DAX Calculated Column for Line Sales, then 2) Measure for Total Sales.
17. (28:56) Second look at ROW Context to calculate line sales in each row of the table.
18. (30:25) SUMX Function, Iterator Function, to create Total Sales Measure. This is 1 Step Method.
19. (32:43) Should you use DAX Calculated Column? Or SUMX Iterator function Measure? What are trade offs?
20. (33:37) Step # 3: Build Reports. Build First Data Model PivotTable to show Year Month Sales Report. Start PivotTable from within Power Pivot for Excel window.
21. (34:48) PivotTable Fields task pane for a Data Model PivotTable. Which table is from Data Model?
22. (36:37) What is Filter Context? Visuals and explanations.
23. (38:44) How Filter Context helps with Big Data.
24. (39:55) Why some columns are not needed in PivotTable list.
25. (39:55) Why Implicit Measures are inefficient and cause problems.
26. (41:50) Hide Fields. Hide From Client Tool. Hide in Report View.
27. (42:57) Sort Month Names in Data Model.
28. (43:41) Step # 4: Visualize. Create Line Chart for Year / Month Sales to see sales trends over time.
29. (45:27) Create Power Pivot Data Model PivotTable from Excel Data tab.
30. (46:39) Create Regions / Year Sales Report.
31. (47:02) Create DAX Formula for YOY % Change. Learn about the DAX functions: CALCULATE, SAMEPERIODLASTYEAR, DIVIDE, HASONEVALUE and IF.
32. (47:54) CALCULATE function. Calculate Total Sales for last year.
33. (53:41) Create DAX Formula for Average Monthly Sales by Product and Year.
34. (54:54) Learn about VALUES function to get a unique list and deliver a table.
35. (55:25) Create AVERAGEX formula.
36. (56:33) Context Transition explanation and diagrams to learn how AVERAGEX and VALUES and the Total Sales Measure calculates average monthly sales with a much simpler formula than in the Excel worksheet.
37. (59:07) Warning about Context Transition. Why aggregate formula yields wrong answer in Iterator function, but Measure yields correct answer.
38. (01:00:15) Hidden CALCULATE function in each Measure. CALCULATE performs Context Transition.
39. (01:01:00) Include Zero Values in average using the IF Function in the second argument of AVERAGEX.
40. (01:02:52) Create Cross Tab Report with Data Model.
41. (01:03:13) Create Frequency Distribution with YOY % Change.
42. (01:04:00) COUNTROWS DAX function. Super Charged COUNTIFS and FREQUENCY.
43. (01:04:50) Using Variables to DAX Formulas. YOY % Change for Number Transactions.
44. (01:07:45) DAX Formatter web site.
45. (01:08:08) Frequency Data Model PivotTable.
46. (01:08:39) Step # 5: Get New Data and Refresh.
47. (01:11:33) Publishing to Power BI Online and making Report from a blank Excel Workbook.
48. (01:13:46) Comparing a Power Pivot Data Model PivotTable to a Standard PivotTable.
49. (01:15:28) Summary.
50. (01:16:04) Next Video

[PunmasterSTP]

Power Pivot,
DAX is lit,
Just add some data,
And that is it!

Build a model,
No need to throttle,
Your imagination
And productivity.

All you need is a key,
To Power BI,
Publish your work,
If you don't it's a cry

-Ing shame,
Don't do things the same,
Way.

Use your skills,
Put on the bells
And whistles will blow.
Now it's time to go...

To the next video.

😎

[KevinStudent]

Based on my personal experience, you only have about 100 actual viewers, but we all watch each video 5, 000 times.

Cannot thank you enough for the incredible work and help you’ve given to so many people (which is obviously way more than just 100)!

[excelisfun]

Comment from Geert Delmulle
:
Mike,
Here's the comment YouTube doesn't seem to like me to post online: you deserve it.
It's being deleted wherever I post it. To bad for that.

My comment:
WOW!! Just sat through the whole thing in one sitting.
This video series is the ultimate overview series!

All the golden knowledge nuggets neatly laid out in a brilliant string of action-packed pearls.

Every bit of explanation, every interactive simulation is crafted to perfection.

And the perfectly timed animations are the cherries on top of this beautiful cake.

And it is such a large cake, and there are so many cherries!...

The people who will learn from this have it easy: all that knowledge laid at their feet... My advise to them: open your minds and ladle it in!
This is pure gold, studded with diamonds and pearls...
Even for a guy like me who knows all the things you said in this video (OK, except fot that formatting website :-), this is wonderful!
If at some point someone wants to take over what I do, I point them to this series, make 'm watch it, and then I'll give them a thorough exam.
And if they pass, then we'll talk...
Super great stuff, Mike. Fantastic and awesome!
Like I said before: this feels like 2018 2.0, ... and then some!

Best Regards,
Geert Delmulle

[sane]

Most of the people on youtube make long videos and then just keep talking. But you Mike...you are always in action!! you never disappointed. Thanks Mike!

[richardhay]

Excellent. Efficient and comprehensive presentation. Geert's comment says it all. In short: well worth the popcorn!

[kiwikiow]

Not only show how to click here and there to make reports or charts but also describe clearly how power tools work and advantages and disadvantages of each options. You are a great teacher. Thank you very much Mike :)

[johnborg]

AMAZING VIDEO: Besides the fact that I really enjoyed the DAX, I really took some time to appreciate the work from your end to show all those diagrams and sketches while explaining, which it is impossible for someone NOT to understand!!! Thanks Mike for all the hard work you put into the videos for people like me can have the chance to learn!!! : ) : ) I will definitely download this one too!!!

[excelisfun]

Full List of all TOPICS in the video:
1. (00:00) Intro and overview of Standard PivotTable and Data Model Pivot Table
2. (01:15) Intro Song
3. (01:35) Intro to Relationships and SUMX Iterator Calculation to skip helper column in sales table.
4. (02:55) Reminder that building a Data Model and Learning DAX takes more time than a Standard PivotTable.
5. (03:06) 5 Steps in the Data Analysis Process
6. (03:38) Step #1: Get Data using Power Query.
7. (03:50) Look at data source for this project: Excel Tables and “.CSV” files.
8. (04:42) Reminder: Power Pivot and Power BI is tool to use for Big Data.
9. (06:12) Show Power Pivot Ribbon Tab.
10. (06:29) Power Query & Power Pivot work together as one tool.
11. (06:56) Power Query to import text “.CSV” files from a folder, including explanation of Applied Steps and automatic queries created.
12. (11:34) Loading data to the Data Model with Import Data dialog box and using the “Only Create Connection” and the “Add this data to the Data Model”.
13. (12:56) Look at data in Data Model. Preview of Power Pivot for Excel window
14. (13:20) What is a Columnar Data? See how Data is compressed to a small size so that the Data Model can handle Big Data.
15. (14:54) Don’t use Add To Data Model button. Use Power Query instead, because it has better data tools.
16. (15:16) Import Excel Tables to the Data Model using Power Query.
17. (15:29) Decimal Power Query Type is the most consistent Power Query Data Type for numbers with decimals, if you want accurate calculations. If the numbers are whole numbers, use Whole Number Power Query Data Type.
18. (17:01) Step #2: Build Star Schema Data Model.
19. (17:06) Open Data Model.
20. (17:17) Create a Power Pivot Date Table.
21. (19:29) Create One-To-Many Relationships between Fact Table and Dimension Tables.
22. (21:48) Create DAX Calculated Column in Date Table for EOMonth. This Column is used later in DAX VALUES function in first argument of the DAX AVERAGEX function.
23. (24:00) Referring to a Field or Column in DAX Formulas.
24. (24:17) First look at ROW Context to make a calculation in each row in a DAX Calculated Column (or DAX Iterator).
25. (25:28) Convention for referring to Fields and Measures.
26. (26:05) Create Total Sales Measure with 2 Step Process: 1) DAX Calculated Column for Line Sales, then 2) Measure for Total Sales.
27. (27:09) Looking up Product Price with Relationship and RELATED DAX function.
28. (28:56) Second look at ROW Context to calculate line sales in each row of the table.
29. (29:05) DAX SUM function to create Measure for Total Sales.
30. (29:25) Assignment Operator for DAX Measures.
31. (30:06) Add Number Formatting to Measure.
32. (30:25) SUMX Function, Iterator Function, to create Total Sales Measure. This is 1 Step Method.
33. (31:39) How a DAX SUMX Iterator Function works to calculate a column of values and then add.
34. (32:43) Should you use DAX Calculated Column? Or SUMX Iterator function Measure? What are trade offs?
35. (33:37) Step # 3: Build Reports. Build First Data Model PivotTable to show Year Month Sales Report. Start PivotTable from within Power Pivot for Excel window.
36. (34:48) PivotTable Fields task pane for a Data Model PivotTable. Which table is from Data Model?
37. (35:30) Show Tables in Active Tab of PivotTable Fields task pane.
38. (36:37) What is Filter Context? Visuals and explanations.
39. (38:44) How Filter Context helps with Big Data.
40. (39:55) Why some columns are not needed in PivotTable list.
41. (39:55) Why Implicit Measures are inefficient and cause problems.
42. (40:44) How to show Implicit Measures. How to delete Implicit Measures.
43. (41:50) Hide Fields. Hide From Client Tool. Hide in Report View.
44. (42:57) Sort Month Names in Data Model.
45. (43:41) Step # 4: Visualize. Create Line Chart for Year / Month Sales to see sales trends over time.
46. (45:10) Hide Measure.
47. (45:27) Create Power Pivot Data Model PivotTable from Excel Data tab. Use the PivotTable dropdown and dialog box because it is much easier than starting PivotTable from Power Pivot window.
48. (46:39) Create Regions / Year Sales Report.
49. (47:02) Create DAX Formula for YOY % Change. Learn about the DAX functions: CALCULATE, SAMEPERIODLASTYEAR, DIVIDE, HASONEVALUE and IF.
50. (47:54) CALCULATE function. Calculate Total Sales for last year.
51. (53:41) Create DAX Formula for Average Monthly Sales by Product and Year.
52. (54:28) Iterators with Tables at a certain grain in the first argument to pre-aggregate values before making an aggregate calculation. Like SUMX and AVERGAEX.
53. (54:54) Learn about VALUES function to get a unique list and deliver a table. Learn about AVERAGEX to iterate over a Month Grain Table to get Monthly Sales, and then tale the average.
54. (55:25) Create AVERAGEX formula.
55. (56:33) Context Transition explanation and diagrams to learn how AVERAGEX and VALUES and the Total Sales Measure calculates average monthly sales with a much simpler formula than in the Excel worksheet.
56. (59:07) Warning about Context Transition. Why aggregate formula yields wrong answer in Iterator function, but Measure yields correct answer.
57. (01:00:15) Hidden CALCULATE function in each Measure. CALCULATE performs Context Transition.
58. (01:01:00) Include Zero Values in average using the IF Function in the second argument of AVERAGEX.
59. (01:02:04) What to do if you accidentally add non-data model table field to PivotTable. Delete incorrectly added table.
60. (01:02:52) Create Cross Tab Report with Data Model.
61. (01:03:13) Create Frequency Distribution with YOY % Change.
62. (01:04:00) COUNTROWS DAX function. Super Charged COUNTIFS and FREQUENCY.
63. (01:04:50) Using Variables to DAX Formulas. YOY % Change for Number Transactions.
64. (01:07:45) DAX Formatter web site.
65. (01:08:08) Frequency Data Model PivotTable.
66. (01:08:39) Step # 5: Get New Data and Refresh.
67. (01:09:27) Update Power Pivot Date Table for new data.
68. (01:10:12) Look at Power Query query to verify that new files are in Data Analysis Solution.
69. (01:10:26) Sharing Power Pivot Data Model with Power BI Desktop.
70. (01:11:33) Publishing to Power BI Online and making Report from a blank Excel Workbook.
71. (01:13:46) Comparing a Power Pivot Data Model PivotTable to a Standard PivotTable.
72. (01:15:28) Summary. Review of Five Steps in Data Analysis process.
73. (01:16:04) Next Video

Four video release dates:



This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.

[chrism]

Non-stop improvement of the team, thanks as always Mike!

[fatjanpaloja]

I'm today DA 100 certified and have a lot experience with MS Power Pivot and Power BI (also have a lot lot to learn ☺️) but I want to thank YOU, because from your videos I got the first steps in 2013.
Wish YOU all the best @Mike Girvin

[jayxpea]

just wow, one of the best demos & explanation i've come across so far!

[pravinshingadia]

Amazing work Mike - you really are one in a trillion. I've watched your videos for years and still can't believe how you do all this work and pass on your knowledge for free. Biden should be awarding you with a medal or something similar for services to your fellow man!

[russstinehour]

Outstanding, this is the best tutorial on PowerPivot I have found. You do a great job of explaining what and how with the right details. Thank you.

[elgennacorda]

Best quote I've heard in this lecture is: "We have one visualization but we visualize" 😆😆😆

Very comprehensive, thank you for sharing this valuable knowledge 💗

[ankursharma]

Token of My Deepest Gratitude (I am still watching/practicing the video, but I already feel immense respect for Your effort)!
Can NEVER THANK YOU ENOUGH!

[muhammadmoiz]

Hi Mike !

You are the most hardworking Excel mentor I have ever seen on youtube, Sir.

This video is pure gold. Every step is explained beautifully.

Keep up the good work, Sir.

[ogwalfrancis]

Very advance, educative, easy to follow and very important video, thanks so much for always being the number one Excel solution expert🙏🙏🙏🙏🙏

[vanxon]

thanks for all. I improve a lot when watching your video.

[garethwoodall]

Haha. Mike, you are my 'Big Data'! Love these visualisations - thank you for sharing, so helpful to learn!

[SUR-NI-VARA]

Appreciate, Nice Tutorial - I can read the pain, the efforts you have taken to reach all of us. keep it bro. Learn a lot, practice makes each on perfect..I will try out myself..Thanks again