D.O.T.S. Power Query and M Intensive Training Course
£1,270.00 – £26,550.00
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
Description
This is the Dual Online Training Solution (D.O.T.S.) version of our Power Query and M Intensive Training Course. This 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
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.
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
Data Loading and Query Dependencies
Duplicating and Referencing Queries
Disabling Data Loading
Using Query Dependencies view
The M Language
M Language Overview
Using the Formula Bar
Using the Advanced Editor
Understading Variable Declaration
Editing M Code
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
Working with Related Queries
Reference vs Duplicate
Disabling Data Load
Viewing Query Dependencies
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
Dealing with Errors
DataFormat.Error
Expression.Error
DataSource.Error
Using the Keep Errors Command
Identifying Rows with Errors by Duplicating Columns
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
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
Additional information
Date | Private Course (Up to 10 People), 3-5 Mar 2022(Tutor-led, Online), 9-11 May 2022(Tutor-led, Online), 11-13 Jul 2022(Tutor-led, Online), 26-28 Sep 2022(Tutor-led, Online), 21-23 Nov 2022(Tutor-led, Online), Private Training for up to 10 Users, Private Training for up to 20 Users, Private Training for up to 30 Users, Private Training for up to 40 Users, Private Training for up to 50 Users, Private Training for up to 60 Users, Private Training for up to 70 Users, Private Training for up to 80 Users, Private Training for up to 90 Users, Private Training for up to 100 Users |
---|