This course serves professionals responsible for transforming raw data into powerful business intelligence solutions. Power Query enables comprehensive data transformation, including reducing datasets, adding calculated columns, and combining multiple data sources for analysis. Power Query makes data preparation and transformation accessible to non-programmer data professionals.
This in-depth course covers Power Query thoroughly and introduces the M language, enabling you to record and replay transformation sequences. This capability proves invaluable when preparing data on weekly, monthly, or quarterly schedules, significantly reducing repetitive manual work. M Code also provides access to advanced data manipulation features beyond standard keyboard command capabilities.
Audience:
This class welcomes users of Power BI, Excel Power Tools, and SQL Server Analysis Services (SSAS). While all three platforms feature comparable Power Query interfaces, instruction uses Excel 2016 software. Power BI and SQL Server users will readily adapt to minor product differences.
The course targets Excel power users, Power BI business intelligence professionals, and intermediate Power Query users seeking to develop deeper expertise and more effective techniques. Consider pairing this course with our Essential Report Building Skills Using DAX course, which complements this material and applies to Excel, Power BI, and SQL Server environments.
Prerequisites:
While extensive DAX formula experience is not required, previous attendance in our "Analyzing Data with Power BI" or "Excel BI Tools" course, or equivalent professional experience, is recommended.
Course Outline:
Power Query Overview
- Power Query and Power BI Integration
- Power Query and Excel
- Power Query Concepts and Architecture
- Power Query Walkthrough and Demonstration
Data Sources
- Querying Relational Databases
- Extracting Data from Files
- Accessing Other Data Sources
Transforming Data with Power Query
- Understanding Query Steps and Dependencies
- Working with Columns
- Filtering Rows
- Sorting a Table
- Changing Values in a Table
- Unpivoting Columns and Rows
- Transposing a Table
- Creating Custom Columns
Introducing M Language
- Writing M in the Query Editor
- Understanding M Language Concepts and Syntax
- Creating Relationships between Tables in the Model
- Working with Lists, Records, and Tables
- Defining and Using Functions
Working with Multiple Queries
- Using One Query as a Source for Another
- Working with Data from Different External Sources
- Appending Data from One Query to Another
- Merging Two Queries
- Creating Explicit Calculated Fields
Appendix: Power Query Recipes
- Calculations
- Table Transformations
- Working with Data from the Web
