Microsoft Excel
Microsoft Excel Power Tools Training
Microsoft Excel
Microsoft Excel Power Tools Training
Excel Power Tools: Power Query & Power Pivot Essentials
This hands-on course is ideal for Excel users who want to go beyond formulas and VLOOKUPs.
You’ll discover how to use Power Query and Power Pivot to manage, clean, and analyse data more effectively, transforming how you work with Excel.
Whether you’re automating a monthly report or building a full analytics model, this refresher will help you streamline your process and build confidence with advanced Excel tools.
This course is ideal for those who:
- Use Excel regularly and want to elevate their data analysis and modelling skills
- Are familiar with basic formulas and PivotTables but want to work more efficiently with large or multiple datasets
The key learning objectives
By the end of the course, participants will be able to implement and manage enterprise-level data analytics using Microsoft Fabric tools like lakehouses, data warehouses, notebooks, dataflows, pipelines, and semantic models. Learners will build upon their existing analytics skills, focusing on deploying analytics assets using Microsoft Fabric components.
Overview of Excel Data Modelling Tools:
Participants will be introduced to Power Query and Power Pivot, gain an understanding of Excel's data transformation and modelling capabilities, and the benefits of using the Excel Data Model for scalable analysis.
Importing and Transforming Data:
Participants will learn to connect to different sources (Excel, CSV, folders), Filtering, splitting, merging, grouping data, Shaping data: pivot, unpivot, transpose and understand Applied Steps and M Code
Data Modelling Concepts:
Participants will learn how to use Star Schema and table relationships, Creating calculated columns vs measures, Creating a structured and efficient model and managing relationships and common pitfalls.
Working with DAX:
Participants will get started with understanding row vs filter context, Aggregation functions: SUM, COUNT, DISTINCTCOUNT, CALCULATE and FILTER: refining your results and Time intelligence functions: YTD, MTD.
Report Building and Final Project:
Participants will Build dashboards with PivotTables and PivotCharts, Create slicers and interactivity, troubleshoot DAX issues and logic errors, Performance tips: efficient queries, avoiding common slowdowns and Final Case Study: Clean, model, and analyse real-world data .
Throughout the course, ample time will be provided for students to follow along with the instructor during demonstrations and labs. The labs are designed to reinforce the concepts learned in each module and provide hands-on practice in applying the skills.
Why Choose us?
As a Microsoft Solution Partner since 2016, we have demonstrated a long-standing commitment to excellence in delivering Microsoft solutions. Our extensive experience and deep understanding of Microsoft technologies enable us to provide tailored training and support that meets the unique needs of our clients. We leverage our partnership to stay at the forefront of industry trends and innovations, ensuring that our training programs are not only relevant but also impactful. Our dedicated team of experts is passionate about empowering organizations to maximize their investment in Microsoft products, making us an ideal partner for your training needs.
Sign up for our Excel Power Tools:
Duration: 1 day
Price: £575
- By Request
- Online
An invoice will be issued. We accept payment via direct bank transfer or card
Frequently Asked Questions
What is the “Excel Power Tools: Power Query & Power Pivot Essentials” course?
It’s a hands-on training course designed for Excel users who want to go beyond basic formulas and VLOOKUPs. You’ll learn how to use Power Query and Power Pivot to clean, transform, and analyze data more efficiently.
Who should take this course?
The course is aimed at people who already use Excel regularly and have familiarity with formulas and PivotTables, but want to work more effectively with large or multiple datasets.
What are the key topics and skills covered?
Participants will learn:
How to import and transform data from various sources (Excel, CSV, folders)
Data shaping techniques like filtering, splitting, merging, grouping, pivot/unpivot, transpose
Understanding and using “Applied Steps” and M code for query logic
Data modelling using Power Pivot (star schema, table relationships)
Difference between calculated columns and measures, and pitfalls in relationships
Basics of DAX: row vs filter context, aggregation functions (SUM, COUNT, etc.), CALCULATE, FILTER, and time intelligence (YTD, MTD)
Performance tips, troubleshooting DAX logic, and building dashboards (PivotTables / PivotCharts)
What are the prerequisites for this course?
There are no strict prerequisites, but the course assumes you have a basic understanding of Excel (formulas, tables, PivotTables) and are comfortable working with data.
Why choose this course provider (DataBear)?
DataBear is a Microsoft Solutions Partner since 2016, bringing deep expertise in Microsoft technologies. They emphasize hands-on training, tailored support, and keeping up with industry trends.
How can I register for the course?
You can register for the course through our website or by contacting our admissions office (training@databear.com) for more information.
