top of page

Fri, Apr 24

|

Mezzo, hotel & business

Power Query - Basics (1 d)

Want to work smarter and focus on the added value tasks of your job? Learn Power Query to automate those monthly repetitive tasks in the most intelligent way by using the Excel built-in professional ETL tool.

Registration is Closed
See other events
Power Query - Basics (1 d)
Power Query - Basics (1 d)

Time & Location

Apr 24, 2020, 9:00 AM – 5:00 PM

Mezzo, hotel & business, Paalsesteenweg 170, 3583 Beringen, Belgium

About the Event

 Training Content

Introduction to Power Query

The students will learn to make queries (connections) to extract data from different data sources.  

Cleaning up data, one of the most cumbersome processes to be carried out on regular basis, is now set up only once and applied each time again when a data renewal is requested. And all this in a 100% reproducible and reliable way.

​The user interface

• Queries pane

• Settings pane 

• Applied Steps pane

• Formula Bar

• Ribbon X

• Formula Bar

• Advanced Editor

Connecting to external data sources

​Data will be extracted from different sources as: 

​• Excel Workbooks

• Text files

• CSV files

Transformations

​A set of basic transformations will be applied on different data sets to learn the intuitive user interface:

• Remove columns

• Remove rows

• Remove duplicates

• Promote first row as header

• Find / replace

• Fill down

• Extract

• Change formats

 ○ upper case​

 ○  lower case

 ○ proper case

 ○ data types

• Split columns​

• Merge columns

• Pivot / unpivot columns

• Monospaced / whitespace

Add columns

Often data needs to be enriched with additional information. In this module the student will add columns.

Column from examples

• Custom column

• Invoke custom function

• Conditional column

• Index column

• Date & time columns

Special queries

Combining data from different data sets is one of the most challenging tasks. Whether data should be appended underneath each other in a single table, or merged next to each other...this task can be automated by using special queries.

Also consolidation of all files located in a folder will be part of the learning path.

• Append queries

• Merge queries

• Combine files

• Duplicate / referenced queries

• Query dependencies

M-Functions and the advanced editor

When adding additional columns, the student will learn how to write formulas by using M-functions.

The internal documentation library of Power Query's M-Functions will be added to the query pane.

• Number.From()

• Date.From()

• DateTime.LocalNow()

• List.Dates()

Practical Exercises

During these practical exercises, the student will learn the topics outlined above.

• Cost-to-Serve Dashboard: combine data from different data sources

 ○ delivery data​

 ○ invoice data

• Budget consolidation​

 ○ append multiple regional budgeting templates located in a folder​

• Consolidation of all worksheets from an Excel workbook​

 ○ append quarterly data​

 Goal

Each Controller/Analyst should sooner or later extract data from different data sources (Excel,TXT/CSV, Databases, XML, JSON, etc...). In Power Query, this is done by using queries or connections. In 95% of the cases, data usually needs to be transformed or enriched for all kinds of business reasons even before a report can be based on it.

 Prerequisites

The training can be followed by using Power Query in Excel or Power BI Desktop.

Microsoft Power Query

Own Laptop 💻 + external mouse 

 

Power BI Desktop (see Microsoft's documentation Get Power BI Desktop).

 

You can get Power BI Desktop in two ways, each of which is described in the following sections:

📖 Documentation

Students will get 2-year access to the on-line in2Facts documentation platform

🚫 Cancellations

Cancellations up to 8 days before the training course are at no charge. 

Cancellations after this date, as well as no-shows, will be liable for the full registration fee.

Tickets

  • Early Bird

    This ticket provides access to the professional training classes of the in2Facts Training Academy.

    €750.00
  • Trainee

    This ticket provides access to the professional training classes of the in2Facts Training Academy.

    €750.00

Total

€0.00

Share This Event

Early Bird Price

€ 650 / day
excl. VAT

First 5 seats are offered at an early bird entrance price.

Price

€ 750 / day
excl. VAT

Also KMO-Portefeuille registered

for providing training services.

Registration nr DV.O217305

Location

Mezzo Hotel - Paal

Perfect location at E313 exit 26 Paal,

just a 5 minute drive from the

cloverleaf (intersection E313 - E314) 

bottom of page