D.O.T.S. Power Query and M Advanced

Description

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. By the end of this course delegates will be comfortable writing M code from scratch.
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.

Our Dual Online Training Solution provides your staff with two modes of training: live instructor-led training is followed with three year’s access to the self-paced version of the same course. The material covered in the live training will be very similar to that covered in the self-paced video training. So, the self-paced training provides a very effective form of post-training reinforcement.

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