Power Query and M Intensive Training Course

Description

Our three-day Power Query and M Intensive Training Course is designed for Power BI users who would like to obtain a throrough grounding in Power Query’s data cleansing and transformation capabilities as well learning the M language. The intensive course starts from the fundamentals and works through to advanced topics. The course consists of three modules (which can be also taken separately):

• Power Query and M Introduction
• Power Query and M Intermediate
• Power Query and M Advanced

POWER QUERY AND M INTRODUCTION

Power Query is a robust data connection and transformation tool which is built into Power BI and which allows you to connect to data from a wide variety of sources and then transform that data into exactly the right shape and format for your reporting needs. In this one-day Introduction to Power Query and M, delegates will learn to use the Power Query interface to perform a wide variety of useful transformations on data, making it ready for use in the data model.

Course Outline

Power Query Essentials

Power Query Overview
Transforming and Loading Data
Queries vs Tables
Working with Applied Steps
Adding Metadata to Steps

Data Sources

Connecting to Files
Connecting to Folders
Connecting to Web Data
Connecting to Databases
Modifying Data Sources
Data Source Credentials
Using PBIDS Files

Manipulating Columns

Navigating Columns
Selecting and Removing Columns
Column Data Types
Data Type Conversion
Splitting Columns
Combining Columns
Extracting Characters

Manipulating Rows

Filtering Text Columns
Filtering Numeric Columns
Filtering Date Columns
Removing Rows
Removing Duplicates
Removing Errors
Replacing Values and Errors
Using Fill Down

Reshaping Data

Transpose
Unpivot Columns
Pivot
Split by Rows

Combine queries

Append Queries
Merge Queries
Join Kind
Left Outer
Inner
Left Anti
Right Outer
Full Outer
Fuzzy Matching

POWER QUERY AND M INTERMEDIATE

This 1-day Power Query and M Intermediate course is designed for experienced Power BI users. Topics covered include: Working with Related Queries; 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

POWER QUERY AND M ADVANCED

This 1-day Power Query and M Advanced course is designed to provide experienced Power BI users with an in-depth understanding of Advanced Power Query Functionality and the M language. Topics covered include: Dataflows; M Language Essentials; Creating Custom Functions In M; Coding Data Source Connections; Time-Sensitive Operations; Coding Table Operations; Error Handling; and AI Insights.

Course Outline

Dataflows

Overview of Dataflows
Azure Data Lake
Using Power Query Online
Transferring Queries from Power BI Desktop

M Language Essentials

Why Learn M?
Working with M in the Query Editor
The nature of the M language
The let … in statement block
Comments in M
Variables in M
Built-in (primitive) data types
Complex data types
Lists, Records and Tables
Exploring M Using #Shared

Creating Custom Functions In M

Converting a Query to a Function
Referencing parameters
Using Invoke Custom Function
Understanding Function Syntax
Writing you own Functions
Defining Input Parameters
The Goes-To Operator
Defining The Function Body
Using Optional Parameters
Calling Functions Inside Queries

Coding Data Source Connections

Csv.Document
Excel.Workbook
Combining Different Source Types
Sql.Database and Sql.Databases
Creating a Development lifecycle Solution

Time-Sensitive Operations

DateTime.LocalNow
Connecting to a Time-Sensitive File Name
Importing the most recent file in a folder

Coding Table Operations

Table.SelectColumns versus Table.RemoveColumns
Table.SelectRows
Table.SelectRowsWithErrors
Table.Transformcolumns
Table.Transformcolumntypes
Table.Unpivotcolumns
Table.Unpivotothercolumns

Error Handling

DataFormat.Error
Expression.Error
DataSource.Error
Using the Keep Errors Command
Identifying Rows with Errors by Duplicating Columns

Defensive Coding
Using try statements
Raising Errors
Enforcing Business Rules

AI Insights

Summary of AI Insights
AI Insights Requirements
Accessing AI Insights
Selecting a Premium Capacity
Text Analytics
Vision
Using Text Analytics
Detect Language
Automatically Generated AI Functions
Score Sentiment
Extract Key Phrases