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.
Prerequisites
Completion of at least Excel or Power BI training.
Objectives
Transforming data with Power Query
Use the M language
Combining data from different sources
Explaining data refreshment
Learn how to use relationships effectively
Distinguish between the effects of active and inactive relationships.
Teaching method
Lectures - Demonstrations - Guided and individual exercises
Contents
Chapter 1 - Confirming fundamental M concepts
Using the M language
Discover the differences between Excel and Power BI
Understanding the Power Query editor
Learn how to document Power Queries
Set up a naming convention
Using variables
Chapter 2 - Querying data sources
Understanding data access functions
Discovering the impact on data refreshing
Optimizing data querying
Chapter 3 - Editing queries
Using the M language and the advanced editor
Using the main binary functions
Using the main association functions
Using the main comparison functions
Chapter 4 - Using advanced queries
Using date and time functions
Using DateTimeZone functions
Using duration functions
Learn to use error functions
Chapter 5 - Using query parameters
Creating parameters in your queries
Learn how to use the Power BI model with parameters
Discover how to diagnose errors
Chapter 6 - Creating a date table
Use variables to define bounds
Use functions to create references
Apply the necessary transformations
Properly document your code
Chapter 7 - Optimizing the relationship diagram
Understanding the impact of active and non-active relationships
Understand the meaning of filtering and how to use it
I accumulated more than 10 years of experience in IT before coming to Quebec. I have taught computer science and have been in charge of several business intelligence projects and computer science projects to help with decision support. I have a strong experience as an analyst of business intelligence solutions, ranging from the specification and analysis of needs to the generation of dashboards. My passion for this job, my great technical skills and my sharp analytical mind add to my skills to make me a valued and precious resource for my organization.
Nicolas Georgeault
Trainer
My presence among the Microsoft community over the last 25 years has earned me the title of MVP 13 times by Microsoft. I particularly appreciate the space that's offered to explain things in a simple way and to ensure that knowledge is updated according to transformations. After all these years, I can say that my great understanding of the platforms enables me to anticipate their evolution while keeping a pragmatic eye on the challenges that come with this modernization. For these reasons, I am pleased to be part of our Microsoft training program.
Frédéric Paradis
Certified Trainer and Cloud Architect
As a certified Microsoft trainer, Frédéric describes himself as a Cloud magician who easily navigates the mythical space between technology and reality.
Marc Maisonneuve
Training program director
Marc Maisonneuve has acted as a Training Program Director, professional effectiveness trainer and user tools practice leader at AFI for several years. Mr. Maisonneuve is known for his analytical skills, his legendary calm and his undeniable desire to encourage people to develop their skills. He has the ability to present technological solutions in a natural way and to adapt them to the concrete needs of the workplace.
