Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships, Data Modeling& Visuals

Excel Data Analysis 03 files:
Learn about Power BI Desktop (and online too) as the perfect compliment to your Excel Data Analysis tools. Topics in the video:
1. (00:00) Intro & Overview
2. (04:12) Look at data for project
3. (04:54) Look at New Power BI file and tour of Power BI user interface
4. (06:13) Power Query to import two tables from one Excel file
5. (08:48) Import and append data from multiple Excel files
6. (09:09) Why the From Folder Power Query option when you have Excel files is more complicated than when you have Text files
7. (14:42) Power Query Excel.Workbook function to get Objects from an Excel file, using a Custom Column
8. (19:50) Append all tables from Excel files
9. (20:37) Using Locale Data Type feature to match imported dates with Regional Settings on Computer. Allows any country to use any other country’s dates and number settings
10. (22:10) Close & Apply Fact Table to Data Model and watch Relationships that are automatically created
11. (22:24) Create Date or Calendar Table using DAX Table functions. CALENDAR function. Also see DATE, YEAR, MIN and MAX functions
12. (23:13) Definition of Date Table
13. (25:38) Mark Date Table as a Date Table
14. (26:21) Create DAX Calculated Columns in the Date Table for Month, Month Number and Year
15. (26:47) Row Context in a Calculated Column
16. (27:02) FORMAT function to create Month Name
17. (27:48) Sort BY Column feature to get Month Names to sort correctly
18. (29:13) Create Relationship for Date field
19. (29:23) Create Date Table with GENERATE and ROW functions. See variables and fiscal period formulas in this DAX Table Function. Amazing formula!!!!
20. (32:05) Create Total Sales Measure in Data View (Table View) using the SUMX & RELATED functions
21. (32:52) Measure Icons in Power BI and Power Pivot
22. (34:52) Create Measures in Report View on a Test Measure Page
23. (35:08) Matrix Visualization is VERY similar to a PivotTable!
24. (36:01) First look at the Format area and the Paint Roller option
25. (36:41) Move Totals from Top to Bottom
26. (37:15) Total Units Measure
27. (37:58) Frequency Measure with COUNTROWS function
28. (38:50) EOMONTH function in DAX Calculated Column in Date Table
29. (39:24) Why we need EOMONTH function for accurate average monthly sales formula
30. (40:00) Average Monthly Sales Measure with AVERAGEX, VALUES function
31. (41:45) YOY % Change DAX Measure using Variables, CALCULATE, SAMEPERIODLASTYEAR, IF and HASONEVALUES DAX functions
32. (46:47) Introduction to ALL and ALLEXCEPT DAX functions
33. (47:33) % of Sales Grand Total Measure using CALCULATE, DIVIDE and ALL DAX functions.
34. (48:10) Why ALL on Fact Table removes all filters in the Data Model
35. (49:16) % of Year Sales Measure using CALCULATE, DIVIDE and ALLEXCEPT, IF and HASONEVALUE DAX functions
36. (50:43) Hide fields in tables from the Report View
37. (52:09) What Relationships do
38. (52:36) Visualizations in Power BI
39. (53:20) Create Year Month Total Sales Matrix and Line Chart
40. (54:46) Turn off concatenated
41. (55:11) Measures in Tooltips
42. (56:58) Edit Visualization Title, especially when you use Tooltips
43. (56:35) Amazing Filter Pane in Power BI!
44. (57:41) Edit Interactions between visualizations for Matrix and Line Chart
45. (59:35) Drill Up, Drill Down, and move to a new Level
46. (01:00:39) What Line Charts do
47. (01:00:53) What is a Dashboard? Define Dashboard and what a Dashboard is in Online Power BI
48. (01:02:40) Create Year Metric “Dashboard” on a Page in a Power BI Desktop Report file
49. (01:03:11) What Tables or Matrix visuals do that is different than a chart or other visuals
50. (01:03:29) What Column and Bar Charts do = Compare differences across categories.
51. (01:04:00) Difference between the Clustered Column/Bar and the Stacked Column/Bar
52. (01:05:37) Moving Visualizations
53. (01:07:16) Add Data Labels
54. (01:07:40) Edit Interactions between visualizations for Matrix and Column or Bar Chart: Filtered, Highlight or No Filter
55. (01:09:00) Publish Power BI Report to Power BI Online
56. (01:09:30) Hide Measure Page
57. (01:09:45) What is a Workspace?
58. (01:10:06) Open Power BI Online
59. (01:10:27) Tour of Power BI Online
60. (01:11:04) Sharing reports and dashboards with Power BI Online
61. (01:11:45) Build a new Workspace
62. (01:12:05) Create a report from a dataset
63. (01:12:16) One Source of Truth Data Sets for Excel PivotTable
64. (01:12:35) Creating Dashboards in Power BI Online, including sharing a dashboard
65. (01:13:42) Add new Excel and update
66. (01:14:45) Summary
67. (01:15:45) What’s in next video

[excelisfun]

Full List of topics in the video:
1. (00:00) Intro & Overview
2. (00:41) Intro Song
3. (01:01) Compare and contrast Power BI & Power Pivot
4. (01:43) Why Excel people should learn Power BI
5. (02:19) Power BI Desktop used worldwide without the online option
6. (03:11) Power Pivot uses MDX and Power BI uses DAX (Must faster on Big Data
8. (04:12) Look at data for project
9. (04:54) Look at New Power BI file and tour of Power BI user interface
10. (06:13) Power Query to import two tables from one Excel file
11. (07:40) Loading data in Power BI is different than in the Power Pivot tool
12. (08:48) Import and append data from multiple Excel files
13. (09:09) Why the From Folder Power Query option when you have Excel files is more complicated than when you have Text files
14. (10:27) Power Query From Folder to import files
15. (12:45) Close without loading query, to fix a mistake
16. (14:42) Power Query Excel.Workbook function to get Objects from an Excel file, using a Custom Column
17. (15:30) Intelligence and Tab key do not work in Power Query
18. (19:50) Append all tables from Excel files
19. (20:23) Add Data Types
20. (20:37) Using Locale Data Type feature to match imported dates with Regional Settings on Computer. Allows any country to use any other country’s dates and number settings
21. (22:10) Close & Apply Fact Table to Data Model and watch Relationships that are automatically created
22. (22:24) Create Date or Calendar Table using DAX Table functions. CALENDAR function. Also see DATE, YEAR, MIN and MAX functions
23. (23:13) Definition of Date Table
24. (25:38) Mark Date Table as a Date Table
25. (26:21) Create DAX Calculated Columns in the Date Table for Month, Month Number and Year
26. (26:47) Row Context in a Calculated Column
27. (27:02) FORMAT function to create Month Name
28. (27:48) Sort BY Column feature to get Month Names to sort correctly
29. (29:13) Create Relationship for Date field
30. (29:23) Create Date Table with GENERATE and ROW functions. See variables and fiscal period formulas in this DAX Table Function. Amazing formula!!!!
31. (32:05) Create Total Sales Measure in Data View (Table View) using the SUMX & RELATED functions
32. (32:52) Measure Icons in Power BI and Power Pivot
33. (34:52) create Measures in Report View on a Test Measure Page
34. (35:08) Matrix Visualization is VERY similar to a PivotTable!
35. (35:56) Drag and drop fields in Field area
36. (36:01) First look at the Format area and the Paint Roller option
37. (36:41) Move Totals from Top to Bottom
38. (37:15) Total Units Measure
39. (37:32) Add Number Format to Measure
40. (37:58) Frequency Measure with COUNTROWS function
41. (38:50) EOMONTH function in DAX Calculated Column in Date Table
42. (39:24) Why we need EOMONTH function for accurate average monthly sales formula
43. (40:00) Average Monthly Sales Measure with AVERAGEX, VALUES function
44. (40:36) VALUES function to get unique list of months
45. (41:45) YOY % Change DAX Measure using Variables, CALCULATE, SAMEPERIODLASTYEAR, IF and HASONEVALUES DAX functions
46. (46:40) Change Font size for subtotals
47. (46:47) Introduction to ALL and ALLEXCEPT DAX functions
48. (47:33) % of Sales Grand Total Measure using CALCULATE, DIVIDE and ALL DAX functions.
49. (48:10) Why ALL on Fact Table removes all filters in the Data Model
50. (49:16) % of Year Sales Measure using CALCULATE, DIVIDE and ALLEXCEPT, IF and HASONEVALUE DAX functions
51. (50:43) Hide fields in tables from the Report View
52. (52:09) What Relationships do
53. (52:36) Visualizations in Power BI
54. (53:20) Create Year Month Total Sales Matrix and Line Chart
55. (54:46) Turn off concatenated labels for line chart
56. (55:11) Measures in Tooltips
57. (56:58) Edit Visualization Title, especially when you use Tooltips
58. (56:35) Amazing Filter Pane in Power BI!
59. (57:41) Edit Interactions between visualizations for Matrix and Line Chart
60. (58:53) Upper Right Controls for a Visualization, including expecting data as csv file
61. (59:35) Drill Up, Drill Down, and move to a new Level
62. (01:00:39) What Line Charts do
63. (01:00:53) What is a Dashboard? Define Dashboard and what a Dashboard is in Online Power BI
64. (01:02:40) Create Year Metric “Dashboard” on a Page in a Power BI Desktop Report file
65. (01:03:11) What Tables or Matrix visuals do that is different than a chart or other visuals
66. (01:03:21) Visuals provide for immediate visual impact than tables
67. (01:03:29) What Column and Bar Charts do = Compare differences across categories.
68. (01:04:00) What is the difference between the Clustered Column/Bar and the Stacked Column/Bar
69. (01:05:37) Moving Visualizations
70. (01:07:16) Add Data Labels
71. (01:07:40) Edit Interactions between visualizations for Matrix and Column or Bar Chart: Filtered, Highlight or No Filter
72. (01:08:41) Third set of visuals
73. (01:09:00) Publish Power BI Report to Power BI Online
74. (01:09:30) Hide Measure Page
75. (01:09:45) What is a Workspace?
76. (01:10:06) Open Power BI Online
77. (01:10:27) Tour of Power BI Online
78. (01:11:04) Sharing reports and dashboards with Power BI Online
79. (01:11:45) Build a new Workspace
80. (01:12:05) Create a report from a dataset
81. (01:12:16) One Source of Truth Data Sets for Excel PivotTable
82. (01:12:35) Creating Dashboards in Power BI Online, including sharing a dashboard
83. (01:13:42) Add new Excel file to folder and see that everything in Power BI Desktop and in Online will update with new data
84. (01:14:45) Summary of what we did in video
85. (01:15:45) Conclusion and What’s in 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.

[rodrigocustodio]

Mike is the most talented Excel Guru you will ever find. Even the energy flowing from his delivery is amazing. Such a talent for teaching. GOD bless Mike for all the benefit that he has brought to the Data analysis/Excel community with his amazing and incomparable work.

[MostafaAhmed]

thanks for the all series form pivot table to power bi, You are a legend :))

[jeanphilippetombofoe]

Thank you Mike, I am currently preparing PL-300 certification and this is by far the best practical training I have done so far. The case study is close enough to real business situations with the right amount of data to practice a lot of Power BI features. Your explanations are clear and I do like the duration of the video around a little more than an hour which is less intimidating. Merci beaucoup as we say in french :)

[reanalytics]

I have made it a duty to first like, then share to a few friends before watching.
Mike has never disappointed from day one

[rodneyoganga]

The DAX measures are a challenge for me to master but with time, i'll grasp it, and once again IMPECCABLE job with a well articulated tutorial Mr. Mike Garvin

[HusseinKorish]

Amazing ...One video equals a whole powerbi tutorial ... thanks Mike ...this video will be my reference for the future.

[paulsingleton]

Hi Mike, thank you for your four recent tutorials on Excel Data Analysis. As usual, they have been both informative and concise in delivery. 👍 👏

[shaimamustafa]

One of the best really.
I've been struggling with my big data at work and making pivots was taking a lot of time.

Thank you for your effort!!

[andrewjohnson]

Finished this vid! Now to the 4th and final of the series!

[mdx]

Mr Gravin, I am going to buy your book just to thank you for your INCREDIBLE job with this channel in general, and with this Data Analysis series in particular.
THANK YOU SO MUCH!!

[GeertDelmulle]

Just as beautiful as the previous one.
Yet again, this one oozes quality and knowledge that are indispensable for the true practitioner.
No one does it better. No one.
You deserve the Oscar for best series for this!!
Love it! Now I'm gonna watch the next one, because I need to catch up. :-)

[coogs]

Great Power BI videos Mike. Thanks for taking the time from your busy schedule and sharing such great knowledge. As we say from back from the 80s..you rock dude!

[RevusDeonauth]

Great video once again. I have been watching your videos since 2013, and they have all been outstanding. Thanks again for another masterpiece!

[knikl]

Really enjoying the series, thank you so much Mike. Really appreciate it :)

[excel_stuff]

Excel Is Fun - but it's also really challenging! I can see I'm going to have to repeat these classes several times to get my head around everything. As always, Mike, thank you for sharing your amazing knowledge so generously.

[jessicaestes]

Amazing video. This is better than any other tutorial I have seen. Thank you so much!

[alexrosen]

A very good and impressive tutorial. Thank you!

[ambotsaimo]

Sir Mike, thank you for sharing your knowledge without asking for any payment. You are amazing! You are the best teacher I ever have as of this time. You are doing great things that helps people like me who wants to improve office work performance and make job easy. God Bless you and your family.

[wayneedmondson]

Awesome Mike! Looking forward to digging into this lesson in detail. As always, thanks for another action packed and information filled video :)) Thumbs up!!