MSPTDA 16: Power BI Desktop Comprehensive Introduction: Power Query, DAX, Dashboards, Publishing

Assigned Homework:

This video is a comprehensive lesson in Power BI Desktop: Power Query to import data, DAX Formulas and Relationships to complete Data Model, Creating Dashboards, Publishing and Sharing Reports.

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

Topics:
1. (00:15) Introduction of what we will do in this video.
2. (02:25) Overview of Excel Power Pivot & Power BI Desktop
3. (02:44) Approximate History of Power BI Desktop :
4. (03:15) Different Versions of Power BI (Different Power BI Products) Available from Microsoft
6. (05:43) List of Charts and Visualizations for your Dashboard (Review from prerequisite classes Busn 216 & 218)
7. (06:02) Overriding Steps for our Project
8. (06:27) Open a blank Power BI File
9. (07:04) Introduction to Power BI Window and User Interface
10. (08:32) Power Query to Import Multiple CSV Files and Clean and Transform Data
11. (13:38) Why we do NOT use Number or Date Fields from a Fact Table
12. (15:57) Import Dimension Tables from a Single Excel File
13. (18:09) Merge Snow Flake Dimension Tables into dProduct Table
14. (19:30) Do NOT import to Data Model (Uncheck Enable Load)
15. (20:22) Old Relationship View & New Relationships View with Properties & Better Selection Capability
16. (20:41) Steps to create Date Table using CALENDAR DAX Table Function & Calculated Columns. See many DAX Functions such as CALENDAR, FORMAT and others.
17. (25:58) Sort By Column to get Months to Sort correctly.
18. (27:47) Create Fiscal Periods for Data Table, including Helper Column for Sorting Fiscal Period correctly.
19. (33:12) Hide Columns from Report View
20. (34:00) Create DAX Measures and see why we do not use Implicit Measures.
21. (36:17) SUMX DAX Function
22. (38:15) Row Context (how formula calculates for each row in a table or Iterator Function)
23. (40:12) Filter Context (How Measures Calculate and how Tables are Filtered when Measures Calculate)
24. (41:50) Measure for Average Daily Revenue. Learn about Context Transition. See AVERAGEX Function to iterate at the Daily level.
25. (47:55) Conventions for DAX Formulas with a great tip from Marco Russo and Albetro Ferrari
26. (49:00) More About Filter Context and Context Transition
27. (49:26) Gross Profit Measures
28. (51:48) Refine Data Model in Power Query by Removing Columns in dProduct Table
29. (52:40) Learn about how to Create & Format Visualizations
30. (52:40) Create “Ave Daily GP” Dashboard.
31. (52:40) Create Matrix and add Conditional Formatting
32. (55:29) Create Column Chart and add Conditional Formatting
33. (56:00) Hierarchies
34. (56:52) Drill Down Icons in Power BI
35. (59:09) Create Line Chart
36. (01:00:00) Create Card
37. (01:01:00) Edit Interactions between visualizations
38. (01:02:50) Create “Fiscal Report” Dashboard
39. (01:05:32) Bookmark to save views of a Dashboard
40. (01:06:20) Create “Ave Last 12 Months” Dashboard
41. (01:06:37) DAX Measure for Average Transactional Revenue. See AVERAGEX Function to iterate at the transaction line item level.
42. (01:07:30) Visual of how we change the Filter Context to get dates for a full year backwards.
43. (01:08:25) CALCULATE & DATESINPERIOD & LASTDATE DAX Functions to calculate Measure for Rolling 12 Month Average for Transaction Level Data.
44. (01:12:08) Create “Question” Dashboard. Learn about Ask A Question feature.
47. (01:14:34) Publish to Web with Free Power BI Desktop version and allow public to review Report
48. (01:16:15)Publish and Share with Power BI Pro Account
49. (01:17:44) Source Data Changes and Refresh
50. (01:18:18) Summary

[excelisfun]

Please help support these epic free education resources with comments, Thumbs Up and Sub : )
P.S. At 14:48.. It should be " IMPLICIT Measures will NOT show up if you download in Excel."

[redherring]

You have no idea how many lives you are changing. Thank you so much for everything. My career has turned around because of you.

[szpyrkowski]

I don't believe how can you both have so much knowladge&skills as well as present this in this super exciting way. Awesome job.

[LeilaGharani]

Amazing! Thank you Mike for this super comprehensive and practical introduction.

[suannyuenfann]

I started and ended the day with you almost every day in the last 2 weeks.
Thank you very much for creating such valuable content but free on Youtube.

[liztrama]

OMG!! It took me one full day to watch this video and follow along with Mike step by step to complete this file. I had to stop and rewind the video many times to understand and complete the DAX formulas. I learned how to create a table, new columns, Dax formulas, different visualization with charts, cards, matrix, and many more. This was sure a comprehensive video. At the end of the day, I had a huge headache, but it was worth it. THANKS A MILLION MIKE!!! You make learning fun.

[sajidmuhammed]

Most comprehensive and all-in introduction to Power BI. Its a tour de force, highly recommended and must watch for beginners and intermediates too. What an achievement!

[excelisfun]

From our Teamate, pa beader, we want to Define the terms used in the video with more accuracy (In this video I used the term "Dashboard" incorrectly). 1) Report = Power BI File Created with Desktop that contains the Tabs (Pages), Data Sets and Data Model. 2) Each Report is connected to One Data Set. 3) Tab in Power BI File = a Page a set of visuals. 4) Dashboard = a place to pin visuals in Power BI Service website. A Dashboard can contain visuals from different Reports and Data Sets.

[pravinshingadia]

Aewsome effort Mike. Also love that you have timings documented so you can quickly refer to parts you wish to go through again. Your students are lucky to have someone like you!

[suneelkokate]

Sir, I have seen many excel instructors but nobody is as efficient as you, Thank you for your great efforts.

[rodrigocustodio]

Thank you, Mike, for your outstanding classes. You are very talented for teaching, yet you are a very humble guy. your free classes here on youtube are better than paid classes.

[danieltoupin]

Wow, I really love how you show the material. Ultra Clear, with lots of explanation. Great job!

[excelisfun]

Homework Files are now posted. So you can watch and study the Power BI Video, then if you want to practice... you can have more fun and power!!!!

[emmafeng]

Thanks heaps Mike!!! I learnt my Excel skills from your channel and three years later I start the Power BI learning journal from your channel!! You are so great. ❤️

[blackwhite]

Man I watched your Excel for Business Intelligence and I must say you are good. You present the material in a clear, concise and easy to understand way. You just earned a sub.

[rodrigocustodio]

Thank you Mike, I speak in behalf of all Excel users that have benefited from your amazing, and outstanding videos.In Every single class, in every single lesson, you have these amazing energy in your voice and awesome delivery. My 10 years old daughter when heard you presenting another class said that you are somebody who enjoy what you do, very true remarks from my girl! All the best for you and your family, and as always, this is one more great video !

[brianxyz]

Mike, when creating a calendar using DAX you can add in Quarter by using Format just like you did for Month.

Quarter = Format(dDate[Date], "Q")

Another way to prevent hidden date tables from being created is to turn off that functionality.

Options and settings > Options > Current File: Data Load > uncheck Auto Date/Time under Time Intelligence

[Nguyenchihien]

Most of my skills about power query, pivot, BI and other excel tricks are learned from Excel fun channel. Thank you very very much for your relentless effort of updating new knowledge about excel world.

[LongTimeTTFan]

Thank you so much, Mike. Your wisdom, your knowledge and above all, your spirit of ALTRUISM is exceptional and always inspiring.

[cbaide]

I felt that I had not completely grasped the knowledge this video is supposed to convey so I watched it a second and then a third time. Now I can say I have grasped completely all the knowledge Mike Girvin is transmitting via this video. By watching the video several times I also can appreciate all the time and effort put by Mike to shoot a video like this. Every detail to deliver a clear message is taken care of: zooming, panning, highliting text with different colors, differente shapes pointing to messages, pdf notes, etc. This is true for all the videos Mike uploads to Youtube. I feel compelled to say what an amazing job Mike G. does...and it is for free!. Thank you Mike, I admire you very much. I wish you and your family a very happy new year.