en
fr
IT Training

Excel 2019/365 Power Query – Level 2

Thanks to a functional and non-developmental interface, Power Query facilitates the work of those who need to analyse data from multiple sources to make it understandable to everyone in Excel and/or Power BI. Participants in this training course should have a very good knowledge of Excel and a basic knowledge of Power Query.

Who should attend this course?

Thanks to a functional and non-developer interface, facilitate the work of those who have to make data from multiple sources analyzable on a daily basis in order to make it understandable for everyone in Excel and/or Power BI.

Prerequisites

Participants should have a very good knowledge of Excel and basic knowledge of Power Query

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)

Quick revisions on

  • The Power Query vs Excel environment (know how to drag from one to the other)
  • Connection to multiple Excel data sources, csv/txt
  • Data transformation operation
  • Manage columns and rows to keep
  • Rename headers / Sort/Filter / Split columns
  • Create header row
  • Adapt data types to analysis needs (+ automatic detection)
  • Replace/Adapt Values
  • Column duplication
  • Format columns
  • Adding column from selection

Learn how to process data from sources other than Excel & csv/txt

  • Connect to web, PDF, Access sources

data modeling

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

Advanced data processing in power Query

  • Group data (subtotals)
  • Transpose / Rotate / Unzip columns and rows
  • Perform calculated operations
    • On Numbers (Stat, Rounding, Information…)
    • On Dates (Year, Anniversary,… )
  • Group columns to create structured data
  • Create Merge and Add queries

Operation on columns

  • Adding Custom Column
  • Conditional column
  • Index column
  • Retrieve data from columns

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

1 Day

Languages

FR / NL

Price

€350,00 + 3% VAT

Location

Classroom Courses

Schedule

Guaranteed to run

French courses
14/3Book
06/5Book
10/8Book
03/10Book
23/11Book
22/12Book

Share this course on

Book your training

Enter your information to confirm your booking.

    You might also be interested in

    ExcelExcel
    Excel 2019/365 – Level 1
    22/9 - 23/9, 20/1 - 21/1, 10 ...
    Being able to create tables and manage the calculations, formatting and page layout of the tables. The new Office interface,…
    ExcelExcel
    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…
    ExcelExcel
    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…
    ExcelExcel
    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