Excel 2019/365 Power Query - Level 1

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

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 in our training courses should have a 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!

• Familiarize yourself with the Power Query environment
• Connect multitudes of data sources
• Data transformation operation
• Add Columns Operation
• Practical exercises  and real-life scenario + Tips and Tricks

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 a multitude of data sources

• Know how to import data from different sources into Power Query
  • Excel
  • csv text

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
• Perform calculated operations
  • On Numbers (Stat, Rounding, Information…)
  • On Dates (Year, Anniversary,… )
• Group columns to create structured data

Add Columns Operation

• Adding column from selection
• Adding Custom Column
• Column duplication
• Format columns
• Retrieve data from columns