Power Query and M Intermediate

Description

This 1-day Power Query and M Intermediate course is designed for experienced Power BI users. It shows delegates how to control and customize the operations initially performed using the Power Query interface. Topics covered include: Data Loading and Query Dependencies; Using the Advanced Editor; Using Parameters; Parameters and Incremental Refresh; Assessing Data Quality; Reducing the Cardinality of Columns; Creating Non-Tabular Queries; and Dealing with Errors.

Course Outline

Data Loading and Query Dependencies

Duplicating and Referencing Queries
Disabling Data Loading
Using Query Dependencies view

Using the Advanced Editor

M Language Overview
Using the Formula Bar
Using the Advanced Editor
Understanding Variable Declaration
Editing M Code

Using Parameters

Parameters Overview
Parameterizing Data Source Information
Parameterizing Date Ranges
Applying Parameters as Filters
Using Merge Queries to Extend Parameter Filtering
Using Static Lists for Parameter Input
Using Dynamic Lists for Parameter Input
Using Parameters in a Template

Parameters and Incremental Refresh

What is Incremental Refresh
RangeStart and RangeEnd Parameters
Filtering the Main DateTime Column
Ensuring that Query Folding Will Occur
Configuring Incremental Refresh

Assessing Data Quality

Show Whitespace
Column Quality
Column Distribution
Column Profile
Column Profiling Based on Entire Dataset
Using the Table.Profile Function

Reducing the Cardinality of Columns

Overview of Column Cardinality
Optimizing Data Types
Isolating Data Types by Splitting
Reducing Cardinality by Grouping

Creating Non-Tabular Queries

Returning Earliest and Latest Dates
Replacing Errors with an Average Value