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

Excel Data Analysis 03 files: https://excelisfun.net/files/EDA-03-E... https://excelisfun.net/files/EDA03-Re... Download free Power BI Desktop: https://powerbi.microsoft.com/en-us/d... 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