PowerQuery course

The PowerQuery course will provide you with incomparably faster and better data manipulation, regardless of which source the data comes from!

PowerQuery marks a revolution in data processing automation. By knowing this tool, you can save a lot of time previously spent in Excel (or other similar tools) to refine and correct the data prior to analysis.

PowerQuery provides an intuitive and simple interface that enables you to create automated data processing scripts quickly. This type of automation saves you time and money, and allows you to do more analysis of the data itself, rather than doing repetitive daily, weekly, or monthly data processing!

PowerQuery is an integral part of Excel and PowerBI, so by knowing this technology you become a data processing expert in the two most used data analysis tools!

Courses most often taken in combination with the PowerQuery course:

PowerBI basic course

A basic course in which one goes through all the most important elements of the PowerBI ecosystem. PowerBI represents a revolution in standalone data analytics and provides business users with the ability to create and analyze advanced reports/models themselves, which previously required an entire IT department.

DAX course

DAX is a programming language that manages the entire data model within the PowerBI system. Knowing DAX you can create complex dynamic calculations, much more powerful and faster than all you have done before within the Excel interface.

Course goals

Introduce the participants with the advanced options of the PowerQuery ETL (extract, transform, load) tool. PowerQuery is a standard tool for ETL which comes already installed with the Excel 2016 version. PowerQuery is also a part of the PowerBI tool.

Participants

This course is intended for all the participants who often have to process unstructured data from various different sources (.txt, .csv, Excel, web, SQL databases, etc.), and in that process, they need to refresh, filter, clean or change the structure of the data.

Notes

Course can be lectured through PowerBI or Excel interface. In case of using Excel option, participants need to have at least Excel 2016 installed or PowerQuery add-on in case of prior versions. It is advisable to use Excel 365 if possible.

Course content

Intro

What is PowerQuery, the idea behind the tool, and why use it to prepare data
PowerQuery interface overview
- Query pane
- Applied steps
- Preview pane
- The most important Tabs
How PowerQuery tool work
- Record transformation steps
- Sequence of execution
- Differences from VBA

Data Import

Web
- Connecting to online data sources
- Data retrieval automation
Excel files
- Objects within an Excel file
- Main data transformations
CSV/Text files
Folder
- Connecting to multiple Excel files within the same folder
Database (SQL)
- The difference between import and direct query
- Switching to SQL database (Query folding)

Combining Different Data Queries

Appending Queries
- Merging multiple identical data structures
Merging Queries
- Merging different data structures via a key column
- Most similar to the VLOOKUP function in Excel, with additional useful options
Query dependency view
- Branching view of all queries within a single PowerBI or Excel PowerQuery model

Data Manipulations and Transformations

Data types
- Number, Text, Date, Binary, ...
Column operations
- Splitting, Merging, Rearranging, Removing, Adding, Conditional columns
- Column transformations
  - Cleaning, Remove/Replace values or errors, Fill Down/Up
- Filtering
  - Text filters, Numeric filters, Sorting
- Pivot vs Unpivot data
  - A very useful option for processing "curved" data structures that most often come from Excel exports
- Group By
  - An option similar to the PivotTable, just within the PowerQuery tool

M language

Basics of M language
- let/in syntax
- Transformation steps
M language structure
- M code reading sequence
- Using predefined functions
Parameters
- Defining and using parameters to speed up data processing and source location replacement

M custom functions

Basics of functions
- Why use and where they bring the most benefits in data processing
Creating functions
- Basic syntax
- How to change a query to a function
Combining functions and parameters when creating automated scripts

For more guidance about general course structures and ways of course handling please follow this link.

Why choose us?

We have been using the PowerQuery tool for over 8 years, and we are still using it with our clients daily.

In this course, we were able to synthesize the most common problems that our clients encounter in processing different data structures. Therefore, in most examples, you will find similarities with repetitive tasks that you encounter in your business.

Based on our experience and knowledge, we have created an advanced online PowerQuery course with over 8 hours of video material, and we also share our experience through our blog and Youtube channel. This way, we make our knowledge transparent and allow you to see the values you can acquire by working with us in advance.

Course information
Course duration	8h
Course difficulty	basic to intermediate
Minimal attendees	4
Maximal attendees	25
Price per attendee (netto) *	230,00 €
Course first time held:	2017.

*Additional discount is offered for each new attendee. If you have more attendees to apply for the course, or you would like to combine multiple courses, please contact us through the form below. Based on your inquiry, we will send you a more personalized offer.