£295.00
Our self-paced 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):
Our Power Query and M Self-Paced 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):
Our self-paced online courses help both beginners and experienced users extend their Power BI skills. These short courses convey important topics via step-by-step demonstration and users are provided with all of the necessary resources to replicate the techniques shown as they learn.
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.
Power Query Overview
Transforming and Loading Data
Queries vs Tables
Working with Applied Steps
Adding Metadata to Steps
Connecting to Files
Connecting to Folders
Connecting to Web Data
Connecting to Databases
Modifying Data Sources
Data Source Credentials
Using PBIDS Files
Navigating Columns
Selecting and Removing Columns
Column Data Types
Data Type Conversion
Splitting Columns
Combining Columns
Extracting Characters
Filtering Text Columns
Filtering Numeric Columns
Filtering Date Columns
Removing Rows
Removing Duplicates
Removing Errors
Replacing Values and Errors
Using Fill Down
Transpose
Unpivot Columns
Pivot
Split by Rows
Append Queries
Merge Queries
Join Kind
Left Outer
Inner
Left Anti
Right Outer
Full Outer
Fuzzy Matching
Duplicating and Referencing Queries
Disabling Data Loading
Using Query Dependencies view
M Language Overview
Using the Formula Bar
Using the Advanced Editor
Understading Variable Declaration
Editing M Code
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.
Reference vs Duplicate
Disabling Data Load
Viewing Query Dependencies
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
What is Incremental Refresh
RangeStart and RangeEnd Parameters
Filtering the Main DateTime Column
Ensuring that Query Folding Will Occur
Configuring Incremental Refresh
Show Whitespace
Column Quality
Column Distribution
Column Profile
Column Profiling Based on Entire Dataset
Using the Table.Profile Function
Overview of Column Cardinality
Optimizing Data Types
Isolating Data Types by Splitting
Reducing Cardinality by Grouping
Returning Earliest and Latest Dates
Replacing Errors with an Average Value
DataFormat.Error
Expression.Error
DataSource.Error
Using the Keep Errors Command
Identifying Rows with Errors by Duplicating Columns
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.
Overview of Dataflows
Azure Data Lake
Using Power Query Online
Transferring Queries from Power BI Desktop
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
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
Csv.Document
Excel.Workbook
Combining Different Source Types
Sql.Database and Sql.Databases
Creating a Development lifecycle Solution
DateTime.LocalNow
Connecting to a Time-Sensitive File Name
Importing the most recent file in a folder
Table.SelectColumns versus Table.RemoveColumns
Table.SelectRows
Table.SelectRowsWithErrors
Table.Transformcolumns
Table.Transformcolumntypes
Table.Unpivotcolumns
Table.Unpivotothercolumns
Defensive Coding
Using try statements
Raising Errors
Enforcing Business Rules
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
Date | Single User, Up to 10 Users, Up to 20 Users, Up to 30 Users, Up to 40 Users, Up to 50 Users, Up to 60 Users, Up to 70 Users, Up to 80 Users, Up to 90 Users, Up to 100 Users |
---|