en
nlfr
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.

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

publish

Languages

FR / NL

Price

€ 800 + 21% VAT

Location

Classroom Course

Schedule

Guaranteed to run

Sessions in English
Contact us for more infoBook
Sessions in Dutch
22 - 23/2/2024Book
16 - 17/5/2024Book
04 - 05/7/2024Book
05 - 06/9/2024Book
10 - 11/10/2024Book
05 - 06/12/2024Book
Sessions in French
15 - 16/1/2024Book
08 - 09/4/2024Book
10 - 11/6/2024Book
08 - 09/8/2024Book
16 - 17/9/2024Book
19 - 20/12/2024Book

Share this course on

Book your training

Enter your information to confirm your booking.

    You might also be interested in

    ExcelExcel
    Excel 365 – Level 3 Power user
    Contact us for more informat ...
    This 3-day course will train participants into becoming advanced Excel users by developing their skills regarding data handling, advanced functions,…
    ExcelExcel
    Excel 2019/365 – Level 1
    Contact us for more info, 23 ...
    Be able to create tables and manage the calculations, formatting and page layout of the tables.
    ExcelExcel
    Excel 2019/365 – Level 2
    Contact us for more info, 18 ...
    Be able to work with the main functions. Nest functions. Master powerful analytics tools. Create models.
    ExcelExcel
    Excel 2019/365 – Macro
    Contact us for more info, 12 ...
    Do you want to save time on routine tasks in Microsoft Excel? Macros are the answer. In this course the…

    Prerequisite test

    Take the test

    Looking for a tailor made solution?

    Get started with personal training