fr
en
Mastering Power Query in Excel and Power BI
Your data should inform your stakeholders' decisions. With Power BI, structure your analysis and reporting to make indicators understandable and discrepancies visible. Build your ability to independently produce information that supports business performance management.
- Duration
- 4 days 28 hours
- Code
- PQ-EXPI Code
Presentation
Power Query is a powerful data processing tool developed by Microsoft. It is part of the Microsoft Power BI software suite and is also integrated into other products such as Microsoft Excel. Power Query's main purpose is to simplify and automate the process of importing, transforming and loading data from multiple sources into data analysis tools. It helps professionals save time and achieve more reliable results when managing their data.
In our Power Query course, you will begin by learning the fundamentals of data management, particularly ETL (Extract, Transform, Load), a key process in data science.
You will then learn to extract data from a variety of sources and formats, with a particular focus on Excel and Power BI. You will also discover how to manipulate this data to make it usable and meet technical requirements. Finally, you will explore how to merge data for analysis.
The programme also covers key techniques such as combining multiple datasets. This includes lessons on combined queries, best practices for using them and an introduction to the Power Query M language. This 4-day course includes practical exercises to help you master Power Query features in Excel and Power BI.
Objectives
By the end of the Power Query in Excel and Power BI course, you will achieve the following objectives:
- explore and use the basic features of Power Query;
- import or connect to data in Power Query;
- transform data from multiple sources;
- create and update data types and pivoted or unpivoted tables;
- create, load or edit basic queries;
- use Power Query in Excel and Power BI;
- understand the M language and its basic functions.
Program
Chapter 1: introduction to Power Query
- What is data extraction, transformation and loading (ETL)?
- What is Power Query and why use it?
- The main components of Power Query.
Practical exercises:
- Explore the Power Query interface and menus.
Chapter 2: data preparation (basic challenge)
- Extracting meaning from coded columns.
- Using Column from Examples.
- Extracting information from text columns.
- Extracting date and time elements.
- Preparing the model.
Practical exercises:
- Retrieve and extract data from multiple sources, including Microsoft Excel, relational databases and NoSQL data stores.
Chapter 3: combining data from multiple sources
- Appending tables from several specific data sources.
- Appending multiple Excel workbooks from a folder.
- Appending multiple worksheets from an Excel workbook.
Practical exercises:
- Transform and clean data from Microsoft Excel and Power BI.
Chapter 4: combining mismatched tables
- The impact of incorrect combinations.
- Combining tables correctly when column names do not match.
- Combining mismatched tables from a folder correctly.
- Standardising column names using a mapping table.
- Standardising tables with different levels of complexity and performance constraints.
Practical exercises:
- Combine 2 data tables from Excel and Power BI.
Chapter 5: preserving calculation context
- Creating custom columns for conditional, example-based or index-based calculations.
- Grouping rows.
- Using a custom function.
- Combining a query through merge and append operations.
- Duplicating a query.
- Creating a query reference.
Practical exercises:
- Perform calculations in queries to generate statistics;
- create combined queries and append them to produce a single query from multiple sources.
Chapter 6: creating an unpivoted table
- Importing the data file from Excel or Power BI.
- Converting a cross-tabulated data table.
- Unpivoting columns.
- Detecting data types.
Practical exercises:
- Create an unpivoted table.
Chapter 7: advanced unpivoting and pivoting
- Advanced unpivoting of rows and columns.
- Advanced pivoting of rows and columns.
Practical exercises:
- Unpivot and pivot rows and columns.
Chapter 8: introduction to the Power Query M formula language
- Overview of the M language.
- Creating an M query with the Power Query Advanced Editor.
- Expressions and values.
- Simple Power Query M formula steps.
Practical exercises:
- Create a table containing Orders data;
- capitalise initial letters using the Table.TransformColumns function;
- generate the data table.
Audience
This course is intended for:
- decision-makers, data analysts, finance professionals, IT professionals and students.
Prerequisites
The Power Query in Excel and Power BI course requires the following prerequisite:
- basic knowledge of Excel and computer-based data management.
Teaching and assessment methods
- Initial skills assessment
- Training materials provided to participants
- Continuous assessment throughout the course
- End-of-course feedback questionnaire
- Combination of theory and practical application
- Attendance records
- Post-course follow-up evaluation
- Practical exercises
Course highlights
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.
Power Query®, Power BI® and Microsoft Excel® are registered trademarks or trademarks of Microsoft Corporation in the United States and other countries.