Excel Data Analysis 03 files:
https://excelisfun.net/files/EDA-03-ExcelFiles.zip
https://excelisfun.net/files/EDA03-Reports-FinishedAfterVideoFilmed.pbix
Download free Power BI Desktop: https://powerbi.microsoft.com/en-us/desktop/
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