Download Excel START File: https:\/\/ift.tt\/2FrxeX5
\nSecond Excel Start File: https:\/\/ift.tt\/2Dtf4Sf
\nDownload Zipped Folder with Text Files: https:\/\/ift.tt\/2Frxfu7
\nDownload Excel FINISHED File: https:\/\/ift.tt\/2qSnYkx
\nDownload pdf Notes about Power Query: https:\/\/ift.tt\/2FrxwgD
\nAssigned Homework:
\nDownload Excel File with Instructions for Homework: https:\/\/ift.tt\/2qRom2T
\nExamples of Finished Homework: https:\/\/ift.tt\/2Frxgyb<\/p>\n
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.<\/p>\n
Topics:
\n(00:15) Introduction & Overview of Topics in Two Hour Video
\n1. (04:36) Standard PivotTable or Data Model PivotTable?
\n2. (05:51) Excel Power Pivot<\/strong> & Power BI Desktop?
\n3. (12:31) Power Query to Extract, Transform and Load Data to Data Model \u2013 Get data from Text Files, Relational Database and Excel File.
\n4. (25:47) Build Relationships
\n5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
\n6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
\n7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
\n8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
\n9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
\n10. (35:29) Number Formatting for DAX Measures
\n11. (36:35) Data Model PivotTable
\n12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
\n13. (41:50) Show Implicit Measures
\n14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
\n15. (50:14) Drag Columns from Fact Table or Dimension Table?
\n16. (53:30) Hiding Columns and Tables from Client Tool
\n17. (55:52) Use Power Query to Refine Data Model
\n18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
\n19. (01:01:00) Compare and Contrast Calculated Columns & Measures
\n20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
\n21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
\n22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
\n23. (01:13:47) Introduction to CALCULATE Function: Function that can \u201csee\u201d Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for \u201c% of Grand Total\u201d. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
\n24. (01:27:18) DAX Formula Benefits.
\n25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
\n26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
\n27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
\n28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
\n29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
\n30. (01:41:27) Continue making more Data Model PivotTables.
\n31. (01:41:50) Make Data Model Pivot Chart.
\n32. (01:45:10) Conditional Formatting for Data Model PivotTable.
\n33. (01:46:22) DAX Text Formula for title of Dashboard
\n34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
\n35. (01:50:05) Adding New Data and Refreshing.
\n36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
\n37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
\n38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
\n39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
\n40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
\n(02:03:15) Summary<\/p>\n