Course Description:

Power BI (including Power Query & Power Pivot) are the biggest Excel news items in the last 10 years. They give Excel users the power to draw information out of multiple sources, link it together then perform calculations on it that can then be sliced & diced any way you want.

This hands-on course is focused on real-world techniques utilising the amazing capabilities of Power Query.

This 1-day course covers the core elements needed to get an understanding of what Power Query can do. You will use Power Query to extract and shape data from a variety of sources and data layouts. You will learn to consolidate 50 csv files instantly, “un-pivot” data and replace VLOOKUPS with merged queries.

This focused course delves deeper into Power Query and how to get the most from this amazing tool. You will learn how to amend the “M”code generated by the Power Query interface, create reusable custom functions and set up dynamic parameters for your queries.

Course Agenda:

Power Query Introduction

Creating your first Query

Understanding the Power Query Interface

Splitting data columns

Replacing values

Cleaning out leading spaces

Joining text together

Removing unwanted rows and columns

Understanding Data Types

In-depth Power Query

Transforming more complex data sets

Adding calculated columns

Consolidate 50 CSV files instantly

How to “un-pivot” data

How to join multiple sources into one table

How to replace VLOOKUPS with “Merge”

Organising and documenting queries

Advanced Technique with Power Query and an Introduction to M language

Creating a mapping check to ensure all your data is mapped correctly

Using variables for query parameters

Introduction to the Advanced Editor and M language

Creating re-usable custom functions

Setting up a parameter table and a re-usable parameter function

Calendar Creator

Techniques for debugging M Code

Learning Objectives:

Understand how Power Query can save hours of manual effort in extracting and manipulating data

Learn how to consolidate and merge data sets from multiple sources

Understand best practice design to ensure robust and clear processes are adopted

Provides an introduction to Power Query’s “M” language

Certification:

Once after the training you receive course completion certificate from Mangates

Who can Attend?

Excel users & analysts that sepnd time extracting, re-organizing and analyzing data

Anyone interested in saving huge amounts of time in automating the work involved in creating recurring reports and dashboards