Course : Importing, Transforming, and Processing Data with Power Query in Excel, ENI CertificationENI RS7520 Certification Exam
Practical course - 3d
- 21h00 - Ref. PQY
|
Download in PDF format
Share this course by email
 | Importing Data from Various Sources into Power Query |
| Clean, transform, organize, and ensure the reliability of data |
| Combining Multiple Sources into a Single Query |
| Dynamically Configure Queries |
| Edit the M code generated by Power Query |
| Load, refresh, and analyze data in Excel on your own |
Intended audience
Advanced Excel users who want to automate the import, cleaning, transformation, and consolidation of data from various sources (finance, management accounting, marketing, data analysis).
Prerequisites
Proficiency in Excel: common formulas, structured tables, and pivot tables. Comfortable working with Excel, CSV, and TXT files.
Course schedule
1 Introduction to Power Query
- Understanding Microsoft BI Architecture (Excel, Power Query, Power Pivot).
- Identify Business Use Cases for Power Query
- Identify the benefits of automating data processing.
2 Import the data into Power Query
- Import data from Excel, CSV, and TXT files, as well as from web sources.
- Import data from the active Excel workbook.
- Connect to databases (Access, SQL Server, SSAS).
- Distinguish between the different types of sources and imported data.
- Change the source of an existing query.
- Manage the steps involved in importing.
- Create, organize, and manage multiple Power Query queries.
Activités pédagogiques
Hands-on work
Import data from various sources into Power Query, organize it, and manage queries.
3 Clean, transform, and organize data
- Delete columns and rows.
- Apply simple and advanced filters.
- Sort and change the data type.
- Substitute the values and use the functions.
- Convert text and numeric data.
- Split and merge columns.
- Edit, delete, move, and rename steps.
Group discussion
What is the difference between transforming data and structuring it?
4 Ensure the quality and reliability of the data
- Analyze errors and inconsistencies.
- Display column statistics.
- Display data quality information.
- Use column profiling (distribution, distinct values, unique values).
Hands-on work
Identify the columns that contain errors.
5 Add custom columns
- Generate columns based on predefined calculations.
- Design custom calculation columns.
- Create conditional columns.
- Generate columns based on examples.
- Include index columns.
6 Group and aggregate data
- Group the data by category.
- Use statistical aggregation functions (sum, average, count, etc.).
- Summarize and prepare the data for analysis.
Hands-on work
Creating a sales summary table: grouping data, applying aggregations, and generating a summary that can be used for analysis.
7 Combining Multiple Data Sources
- Merge queries.
- Select the queries and columns used for the join.
- Select the type of joint (internal, left, right, etc.).
- Select the columns from the join.
- Add queries (append).
- Add data from multiple files in the same folder.
8 Create dynamic parameters
- Create and configure settings.
- Use parameters in Power Query queries.
- Make queries dynamic and reusable.
Group discussion
What are the advantages of a parameter over a traditional filter? And when should they be used?
9 Edit the code in the M language
- View and read the M code generated by Power Query.
- Understanding the structure of the M language (let / in).
- Use the built-in help for the syntax of M functions.
- Use M functions for text conversion.
- Use M functions for date and time.
- Creating complex conditions in the M language.
- Optimizing a Query by Modifying the M Code
10 Load, refresh, and analyze data in Excel
- Load the data into Excel as structured tables.
- Load data into pivot tables.
- Refresh Power Query queries.
- Configure the loading options.
- Configure and manage Power Query queries directly from Excel.
Hands-on work
From Power Query to Analysis in Excel: Load the data, choose the right display mode (table /TC
PARTICIPANTS
Advanced Excel users who want to automate the import, cleaning, transformation, and consolidation of data from various sources (finance, management accounting, marketing, data analysis).
PREREQUISITES
Proficiency in Excel: common formulas, structured tables, and pivot tables. Comfortable working with Excel, CSV, and TXT files.
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
Advanced Excel users who want to automate the import, cleaning, transformation, and consolidation of data from various sources (finance, management accounting, marketing, data analysis).
PREREQUISITES
Proficiency in Excel: common formulas, structured tables, and pivot tables. Comfortable working with Excel, CSV, and TXT files.
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TEACHING AIDS AND TECHNICAL RESOURCES
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
This programme is an original creation, developed by the teaching teams at ORSYS Formation. Any reproduction, representation, adaptation or use, in whole or in part, without the prior written authorisation of ORSYS, is strictly prohibited. ORSYS reserves the right to take any action necessary to protect its intellectual property rights.
Dates and locations
Select your location or opt for the remote class then choose your date.
Remote class
Dernières places
Date garantie en présentiel ou à distance
Session garantie
This programme is an original creation, developed by the teaching teams at ORSYS Formation. Any reproduction, representation, adaptation or use, in whole or in part, without the prior written authorisation of ORSYS, is strictly prohibited. ORSYS reserves the right to take any action necessary to protect its intellectual property rights.