Excel - Discover Power Query and dynamically connect your data

Duration : 1 day

This training course is aimed at data analysis professionals and BI experts who want to deepen their knowledge of Microsoft Excel and Power BI data models using the M language to connect different data sources, transform data and optimize data models within their organization.

Audience

Professionals, data engineers and data analysts wishing to import, transform and load data in the Power Query editor

Objectives

• Master the skills needed to clean, filter, sort, aggregate, merge and transform data
• Create a Date table
• Master the best practices

Teaching method

Lectures - demonstrations - guided and individual exercises

Contents

• What is Power Query
• Why use it
• Understanding the Power Query editor

• Understanding data access functions
• Data loading options
• Discover the impact on data refreshing

• Transforming basic data
• Transforming text columns
• Transform number columns
• Transforming date columns
• Using duration functions
• Learn to use error functions

• Pivoting and depivoting columns
• Simplify and standardize names
• Adjust column types
• Combine several tables into one
• Using the advanced editor M

• Create a date list
• Transform list into table
• Change data type
• Add columns Year, Month, Day, ...
• Add a conditional column
• Update a date

• Reviewing the best practices in Power Query

Related courses

Power BI - Discover Power Query and dynamically connect your data | AFI Expertise