Power Query and Power Pivot in Microsoft Excel

 

Microsoft introduced Power Query in 2010 in Excel. Today, it is hidden under the hood of ‘Get & Transform’ menu in Excel.

Power Query is an extremely powerful data cleaning and transformation tool which can connect to any data source and bring data in Excel, transform, and make it ready for analysis with Pivot Tables, or Power Pivot or any other Excel report.

Power Pivot is an ordinary Pivot Table which uses a ‘Data Model’ as its source instead of a single flat table in Excel.  Learning Power Pivot involves learning to design a data model and a brand-new formula language called DAX.

Pre-requisite for the course: Candidate should be an intermediate to advance user of Excel. This course is designed for Excel 2016 and Excel for Office 365 users.

 

1. POWER QUERY IN EXCEL

• Know the basic data preparation challenges.
• Differentiate and create fact vs look up table.
• Get familiar with power query GUI.
• Merge tables and append tables.
• Pivot and Unpivot data.
• Extract information from text messages.
• Create a DATE table.
• Understand why a date or a calendar table is required in analysis.
• Combine data from multiple sources.
• Duplicate vs reference query tables.
• Understand different modes of connection. Import vs direct query vs live connection.
• Connect to SQL database – with direct query connection mode and query folding.
• Conditional column and Custom column.
• Alter the power query with M code.
• Parameter query and function.

2. POWER PIVOT

• Pivot table or Power Pivot. What is the difference and commonalities?
• Data Model.
• Fact Tables, Dimension Tables and Star Schema.
• Introduction to DATA ANALYSIS EXPRESSION(DAX) formulas.
• Writing simple DAX code.
  • Dax Syntax, Data Types, Operators.
  • How to refer to Columns and Tables.
• Writing DAX Formulas.
  • Calculated Columns Vs Measures.
  • Using Variables in a DAX Code.
  • Handling Errors in DAX Code.
  • Formatting DAX Code.
• DAX aggregators and iterators.
  • SUM, AVERAGE, MAX, MIN
  • SUMX, AVERAGEX,MINX AND MAXX
• Counting Rows with DAX Functions
  • COUNT, COUNTA, COUNTBLANK,COUNTROWS, DISTINCT COUNT, DISTINCTCOUNTBLANK.
• Information functions.
  • ISBLANK, ISTEXT, ISERROR, ISNUMBER.
• TEXT Functions.
  • CONCATENATE, CONCATENATEX, EXACT, FIND, FIXED, FORMAT, LEFT, LEN, LOWER.
• RELATIONAL functions.
  • RELATED, RELATEDTABLE.
• DAX STUDIO.
  • Evaluate your DAX code with DAX Studio and Evaluate function.
• TABLE functions.
  • What are Table function?
  • Scalar Vs Table functions.
  • FILTER.
  • RELATEDTABLE.
  • ALL, ALLEXCEPT, ALLCROSSFILTERED, ALL SELECTED
  • VALUES, DISTINCT.
• CALCULATE function.
• Evaluation contest & Context transition.
• Filter Context and Row context.
• Convert your Power Pivot to Formulas.
• Cube Formulas.

3. COURSE SUMMARY.

• Excel’s new Array Functions.
• Create Dynamic reports with Array Functions and Data Validation Lists.
• Discussion about Macros and VBA.
• Path Ahead: How can you become an expert in Modern Excel?