Full Advanced Data Analysis and BI Class - Power Query, Power Pivot, DAX, M Code, Power BI and Excel

ExcelIsFun via YouTube

Start learning Write review

Master advanced data analysis and business intelligence through this comprehensive course covering Power Query, Power Pivot, DAX, M Code, Power BI, and Excel across 30+ detailed video tutorials. Learn to define and apply data analysis, business intelligence, and data warehousing concepts while building practical skills in creating reports and dashboards. Develop expertise in using Excel worksheet formulas and standard PivotTables before advancing to Power Query for extracting, transforming, and loading data to various destinations including Excel worksheets, PowerPivot data models, and Power BI. Write M Code to work behind Power Query's user interface and construct efficient data models for both Excel Power Pivot and Power BI Desktop. Explore the columnar database structure for data storage and compaction while mastering basic to advanced DAX code for business calculations using calculated columns, queries, and measures. Build refreshable and shareable dashboards containing sophisticated reports, visualizations, and analytics. Cover advanced topics including query parameters for dynamic data sources, data model optimization, slowly changing dimensions, many-to-many relationships, budget vs actual reporting, ranking functions, moving averages, and statistical analysis. Practice with real-world scenarios involving multiple Excel files, SQL Server databases, cross-tabulated data, and complex business requirements. Gain hands-on experience with Power BI Online for creating dashboards, reports, and managing datasets while learning best practices for data modeling and visualization design.

Syllabus

Free Advanced Data Analysis & BI Course at YouTube (MSPTDA)
Microsoft Power Tools for Data Analysis: Dashboards & Reports. Class Introduction Video. MSPTDA #01.
MSPTDA #02: Data Analysis Business Intelligence Terms: Microsoft Power Tools for Data Analysis Class
MSPTDA 03: Power Query Introduction: Importing & Transformation Data in Excel & Power BI Desktop
MSPTDA 04: Power Query: Import Multiple Excel Files & Combine (Append) into Proper Data Set
Excel Power Query DataSource.NotFound: File or Folder: We couldn't find the folder. Solved! EMT 1662
MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook
MSPTDA 06: Power Query: Merge, Append, & UnPivot – 3 Important Transformations
MSPTDA 07: Power Query: 6 Types of Joins, 6 Types of Merges: 9 Examples
MSPTDA 08: Power Query Group By feature & Table.Group Function (5 Examples)
MSPTDA 08.5: Power Query Group By Unique List or Consecutive Occurrences
MSPTDA 09 Power Query Complete M Code Introduction: Values, let, Lookup, Functions, Parameters, More
Formula.Firewall Error in Power Query & Power BI: Rebuild This Data Combination Solved (MSPTDA 9.5)
MSPTDA 10: Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query Custom Column
Power Query Get Previous Row? Stock Price Change Formula. M Code Lookup. Excel Magic Trick 1546
Two-Way Lookup in Power Query? M Code for Exact & Approximate Match Lookup. Excel Magic Trick 1554
MSPTDA 11: Power Query to Import from SQL Server Database in Excel or Power BI Desktop
MSPTDA 11.5 Which Power Query Steps Are Used in SQL Query Folding? “View Native Query” feature!
MSPTDA 12: Using Locale in Power Query Power BI: Import & Append Text Files from Different Countries
MSPTDA 13: Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report
MSPTDA 14: Power Pivot Intro #2: Amazing Columnar Database Importing Millions of Rows Data to Excel
MSPTDA 15: Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions
MSPTDA 16: Power BI Desktop Comprehensive Introduction: Power Query, DAX, Dashboards, Publishing
MSPTDA 17: Power BI Online: Dashboards, Reports, Excel Workbooks, Data Sets.
MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI
MSPTDA 19: CALCULATE DAX Function & Filter Context & ALLSELECTED & KEEPFILTERS (50 Examples)
MSPTDA 31: DAX Studio to Time DAX Average Formulas: AVERAGEX or DISTICTCOUNT?
MSPTDA 20: Query Parameter for Dynamic Folder Location / Data Source in Power BI & Excel Power Query
MSPTDA 21: Power Query: Reduce Data Model Size, Transformations to Columnar Database Size
MSPTDA 22: DAX Data Modeling to Create Date & Time Dimension Tables, Server Downtime Visualizations
EMT 1493: Excel Formulas PivotTable: Allocate Invoice Header Amounts To Transaction Line Table
EMT 1493 Part 2: Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Table
MSPTDA 23: Two Fact Tables? DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table
MSPTDA 24: DAX Multiply / Divide Data Types Discrepancies Solved!
Power Query & DAX Formulas for Advanced Data Modeling to Sort Slicer by PivotTable Values. EMT1678.
MSPTDA 25: Budget vs Actual, Excel Formula Solution with SUMIFS & EOMONTH
MSPTDA 26: Budget vs Actual in Power Pivot & Power BI Desktop, DAX & Data Model
Excel Magic Trick 1405: Monthly Totals Report: Sales from Daily Records, Costs from Monthly Records
Excel Magic Trick 1407: Data Model & DAX Measures for Monthly Totals Report From Two Fact Tables
MSPTDA 27: SUMPRODUCT & SUMIFS Worksheet Array Formula to Add Units in Many To Many Relationships
MSPTDA 28: Build Power Query Bridge Table in Power BI & Power Pivot for Many To Many Relationship
MSPTDA 29: Order & Ship Date in Fact Table? Side-By-Side & Cross Tab Reports with Worksheet Formulas
MSPTDA 30: Order & Ship Dates in Fact Table: DAX Formulas and Data Modeling to Create Reports
MSPTDA 32: Slowly Changing Dimensions Team-Employee Report with Worksheet Formulas
MSPTDA 33: Data Modeling for Slowly Changing Dimensions with Power Query & PivotTable
MSPTDA 33 Part 2: Power Query & Standard PivotTable To Show All Employees Sales Or Not
MSPTDA 34: Data Modeling for Slowly Changing Dimensions with Power Query & Power Pivot
MSPTDA 35: Power BI & DAX Formula for Data Modeling with a Slowly Changing Dimensions
Excel Magic Trick 1393 PivotTable Show Values As: % Column Total, % Parent Total, % Parent Row Total
Excel Magic Trick 1394: Excel Formulas for: % Column Total, % Parent Total, % Parent Row Total
Excel Magic Trick 1394 Part 2: Formulas for % Parent Total using Wild Cards to Get First Text Item
Excel Magic Trick 1395: DAX Measures for Show Values As % of Grand Total, Column Total, Parent Total
Excel Magic Trick 1449: DAX Measure % Change MOM & YOY Formulas For Incomplete Years
Excel Magic Trick 1396: PivotTable Show Values As: Running Total and % of Running Total (2 Examples)
Excel Magic Trick 1397: Excel Count Formulas for Running Total and % of Running Total & Ogive Chart
Excel Magic Trick 1397 Part 2: Formulas: Monthly Revenue, Running Total & % Running Total & Chart
Excel Magic Trick 1397 Part 3: % Cumulative Frequency Formula & X Y Chart for Irregular Increments
Excel Magic Trick 1398: DAX Formulas for Running Total and % of Running Total & other DAX Tricks
Running Total in Column, Power Query, DAX or Worksheet Formula? Excel Magic Trick 1553
Excel Magic Trick 1417 Excel Functions Ranking Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1418: PivotTables Ranking For Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer
Excel Magic Trick 1420: Ranking in Power Query: Sort, Grouping, Indexes and Custom Columns
Excel Magic Trick 1429: Power Query Ranking with Index, Grouping and MIN Function!!!
Excel Magic Trick 1378: Average of Customer Total Sales by Product: Formulas, DAX or Array Formula?
Excel Magic Trick 1406: Calculate Moving Average with AVEARGEIFS & EOMONTH Functions
Excel Magic Trick 1408: DAX Measure: Moving 12 Month Average on Daily Sales (DAX Formatter too)
Excel Magic Trick 1424: Average, Moving Average at Different Granularity: DAX or Excel Formulas?
Average Last 3 Customer Sales: Power Query, DAX Measure or Worksheet Formula: Excel Magic Trick 1552
Excel Magic Trick 1427: DAX GEOMEANX function: Average Compounding Rate per Period for FV Calc
Excel Magic Trick 1404: Sales Per Working Day by Month Formulas For Three Year Dynamic Report
Excel Magic Trick 1443: DAX Formula for Sales Per Working Day: Power Pivot or Power BI Desktop
Excel Magic Trick 1430: DAX Functions XNPV & XIRR for Irregular Cash Flow Net Present Value & IRR
Excel Magic Trick 1432: 9 DAX Formulas to Build Confidence Intervals CONFIDENCE.T, STDEV.S…
Excel Magic Trick 1434: SAMPLE DAX Function to Take Random Stratified Sample from Data Set.
Excel Magic Trick 1437: DAX Query OR Logical Test IN Operator: Pull Sales Team Data to Excel Sheet
Excel Magic Trick 1439: DAX Measures to Create Cross Tabulated OR & AND Logical Test Sales Total
Excel Magic Trick 1440: OR Logical Test on 2 Columns using DAX IN Operator & DAX OR Function
Bring Tables from Different Excel Sheets into Single Table for PivotTable Report (EMT1622)
Combine Excel Worksheet Data (Not Excel Tables) Into PivotTable using Power Query (EMT 1626)
Power Query M Code Custom Function to Convert 12 Cross Tabulated Data Sheets to PivotTable. EMT 1621
MSPTDA 36: How To Build Sample File Parameter For A Custom Function to Transform 12 Cross Tab Tables
Excel Power Query Remove Blank Rows & Columns in All Excel Files During Import. EMT 1660.