Dive into a comprehensive 3-hour 44-minute video tutorial on Excel and Power BI data analysis, taught by Excel MVP and Highline College Professor Mike Girvin. Master essential skills including sorting, filtering, FlashFill, PivotTables, Power Query, M Code, XLOOKUP, Power Pivot, DAX formulas, and data modeling. Explore Power BI Desktop for creating interactive visualizations and dashboards, and learn to work with large datasets, including importing 7 million rows from SQL databases. Gain practical experience through multiple examples, covering topics from basic Excel functions to advanced Power BI techniques. Download accompanying files to follow along and enhance your learning experience.
Overview
Syllabus
) Introduction.
) What is Data Analysis?.
) Sorting and Filtering. Example 1 & 2.
) FlashFill. Example 3..
) Standard PivotTable with Summarize Values By and Show Values As calculations. Example 4..
) Power Query to import, clean, transform and refresh data and load to Excel Worksheet. Complete Introduction to Power Query. Example 5..
) First look at Power Query’s Functional Language: M Code.
) Power Query to Import and combine 12 tables into one table and load directly to a PivotTable Report & Excel Chart Visualization. Example 6..
) Excel Line Chart Visualization based on PivotTable.
) Data Modeling for Examples 7, 8 & 9.
) XLOOKUP function, PivotTable and Excel Charts to build report and visualization. Example 7..
) Power Pivot, Data Model, Relationships and DAX Formulas to build Data Model PivotTable Reports. Power Query to import Excel data. Complete Introduction to Power Pivot. Example 8..
) Sort By Column Feature for Data Field in Data Table.
) Filter Context for DAX Formulas in Data Model.
) Implicit Measures and Automatic Date Grouping: Dangers of Data Models.
) Mark As Date Table.
) Send Data Model to Power BI Desktop to make Interactive & Sharable Dashboard. Example 9..
) Complete Introduction to Power BI Desktop, Including how to download free app.
) Building Interactive Visuals.
) Stacked Bar/Chart Vs. Clustered Bar/Chart.
) Complete Introduction Power BI Online and Workspaces to share Power BI Desktop files, Excel files, dashboards and data models easily online..
) What is a Dashboard?.
) Connect to Data Models stored at Power BI Online in Excel and Power BI Desktop.
) Refresh Excel Power Pivot, Power BI Desktop and Power BI Online when Source Data Changes.
) Power BI Desktop and Power Query to import 7 million rows of SQL Database sales data and build Interactive Gross Profit Dashboard. Example 10..
) What is a Columnar Database?.
) Idea behind DAX Revenue Formula (Measure).
) Log in to SQL Database, Import 7 million rows of data with Power Query in Power BI Desktop.
) Build DAX Date Table.
) Row Context in DAX Calculated Columns and DAX Iterator Functions.
) Hide Fields in Report View.
) Two-Step DAX Revenue Measure using ROUND and RELATE#D DAX functions.
) One-Step DAX Revenue Measure using SUMX DAX function.
) Compare One-Step and Two-Step Measures. Which is better?.
) Create four Remaining Measures in Report View.
) What is Gross Profit? Two Measures for Gross Profit..
) Create Two Power BI Reports, including Map Report.
) Map Visual.
) Conclusion.
) Closing and Video Links.
Taught by
ExcelIsFun
