IT Training

Excel 2019/365 Power Query Complete

Forget VBA and discover Power Query to automate your data collection. With this tool you can create complex queries without programming! At the end of this course you will be able to create data tables ready for analysis. Participants in this course need to have a very good knowledge of Microsoft Excel

Who should attend this course?

Thanks to a functional and non-developer interface, PowerQuery facilitates the work of those who need to analyse data from multiple sources on a daily basis, making it understandable to everyone in Excel and/or Power BI.

Prerequisites

Very good knowledge of Excel

Forget VBA and discover Power Query to automate the recovery of your data. This tool allows you to create complex queries without programming!

At the end of this training you will be able to create data tables ready to be analyzed with

Excel (Pivot Tables and PivotCharts + Slicer)
Power Bi t(Table/Matrix/Graphics/Slicer)

Familiarize yourself with the Power Query environment

Know how to switch from Excel to Power Query and vice versa
Call Power Query mode from data stored in Excel
Know how to load data in connection/Table/Pivot Table & Pivot Chart mode

Connect to multiple data sources

Know how to import data from different sources into Power Query
- Excel
- csv text
- Access
- Web

Data transformation operation

Manage columns and rows to keep
Rename headers
Sort/Filter
Split columns
Group data (subtotals)
Adapt data types to analysis needs (+ automatic detection)
Create header row
Replace/Adapt Values
Transpose / Rotate / Unzip columns and rows
Make calculated operations
- On Numbers (Stat, Rounding, Information…)
- On Dates (Year, Birthday…)
Group columns to create structured data
Create merge and add queries

Add Columns Operation

Adding column from selection
Adding Custom Column
Conditional column
Index column
Column duplication
Format columns
Retrieve data from columns

Data modeling

Linking different tables in a Data Model (primary keys, cardinalities)
Joins

Discovery of the Data Analysis Expression (DAX) language

Differentiate Formulas and Functions
Essential functions
- Related
- Sum, Max, Min…

Practical exercises and real-life scenario + Tips and Tricks

Practical information

Duration

2 Days

Languages

FR / NL

Price

€700,00 + 21% VAT

Location

Classroom Courses

Schedule

Guaranteed to run

French courses

17/1 - 18/1Book

04/4 - 05/4Book

05/9 - 06/9Book

05/12 - 06/12Book

Share this course on

You might also be interested in

Excel

Excel 2019/365 – Level 1

20/1 - 21/1, 10/2 - 11/2, 17 ...

Being able to create tables and manage the calculations, formatting and page layout of the tables. The new Office interface,…

Excel

Excel 2019/365 – Level 2

27/1 - 28/1, 24/2 - 25/2, 24 ...

Dealing with the important basic functions. Nesting functions. Using the powerful analysis tools. Creating automated templates and macros. The new…

Excel

Excel 2019/365 – Macro

21/1, 25/2, 18/3, 25/4, 20/5 ...

Do you want to save time on routine tasks in Microsoft Excel? Macros are the answer. In this course the…

Excel

Excel 2019/365 Power Pivot

20/1, 24/2, 17/3, 24/4, 19/5 ...

By the end of this course, you will be able to determine which of the two tools, PivotTables and PowerPivot,…

Prerequisite test

Take the test

Looking for a tailor made solution?

Get started with personal training