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.

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 / Unpivot 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…