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

Description

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. This is the Dual Online Training Solution (D.O.T.S.) version of our Introduction to Power Query and M. In this course, 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.

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

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