Master data preparation, analysis, and automation in Excel using Power Query
This 2‑day course enables participants to master Excel and Power Query to analyze data effectively. It covers automating data cleaning, transformation, and consolidation, as well as creating reports and visualizations to support decision‑making.
Is it for you ?
Business analysts, data analysts, accountants, BI managers, and anyone wishing to improve their skills in data manipulation and analysis in Excel and Power Query
Prerequisites
Basic knowledge of Excel, fundamental skills in table manipulation and formulas, access to a recent Excel environment with Power Query enabled
What You'll Walk Away With
- ✓ Import, clean, and transform multi-source data efficiently using Power Query
- ✓ Automate data preparation workflows to improve accuracy and save time
- ✓ Build analytical models and leverage advanced pivot tables for insights
- ✓ Create automated reports and visualizations from continuously updated data
- ✓ Structure and communicate clear, actionable insights to stakeholders
Training content
1 Data preparation and transformation
- Introduction to Power Query and basic concepts
- Importing and consolidating data from different sources
- Data cleaning and transformation
- Creating calculated columns and advanced transformations
- Handling missing data and complex filters
- Introduction to advanced functions and queries
Hands‑on exercise
Importing and cleaning a complex dataset using Power Query. Deliverable: Excel file with prepared queries and data ready for analysis.
Key points & takeaways
- Mastery of basic and advanced Power Query features
- Ability to automate data transformations
- Efficient preparation of datasets for analysis
2 Analysis and reporting
- Data analysis and aggregation with Power Query
- Creating analytical models and pivot tables
- Introduction to automated visualizations and reports
- Workflow optimization and automatic data refresh
- Best practices for reports and dashboards
- Sharing and communicating insights to stakeholders
Hands‑on exercise
Creation of a complete automated report using Power Query and pivot tables. Deliverable: Final Excel file with analyses and visualizations.
Key points & takeaways
- Ability to create automated and reproducible workflows
- Mastery of advanced analysis techniques in Excel
- Skill in producing clear and concise reports
📌 Practical information
Our training sessions are offered in Montreal or Quebec City, in person or in a virtual classroom. Dates and locations are specified when you select your session below. If you have any questions, check out our FAQ.