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:

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.

Lecturer

Krešimir Ledinski

More than 6 years of experience with PowerBI.
Holds Microsoft MCSA Certificate in the field of BI reporting.
Learn more about Krešimir

Get a special quote for your group

By submitting a form you accept Exceed's Privacy Policy
+
Users
+
Workshops
+
Educational videos
+
Educational blogs
Scroll to Top