Dates and location
Pricing
Hours
Dates and location
Pricing
Hours
Description
Learn to create reliable, interactive reports and dashboards that effortlessly extract, transform, load, and analyze data from multiple large and diverse data sources with just a click. This course will guide you in producing interactive Power Pivot charts, dashboards, and flexible Excel layouts, all powered by Power Query automation and Power Pivot data models utilizing the same robust Vertipaq database engine found in Power BI and other Microsoft enterprise software.
Engage in hands-on development using Excel, working through a case study that takes you from raw data to creating a comprehensive analytical reporting solution. Power BI desktop will also be used to demonstrate skills transfer of what you learn in Excel (but is not required for the hands-on work). Join us to enhance your skills and transform your data analysis capabilities!
Speak to an Advisor
To speak to an advisor, please fill out this form.
Schedule
| Date/Time | Description | Location |
|---|---|---|
|
March 17, 2025 9:30 a.m. - 12:30 p.m. |
Introduction, Method and Alternative Approaches
Getting Data: Extract, Transform and Load with Power Query
|
Virtual on Zoom |
|
March 18, 2025 9:30 a.m. - 12:30 p.m. |
Data Modelling: Building Data Models with Power Pivot
|
Virtual on Zoom |
|
March 19, 2025 9:30 a.m. - 12:30 p.m. |
Data Visualisation: Reporting, Exploration and Analysis Using Pivot Charts and Dashboards
Flexible Hybrid Models: Combining Power Pivot Data and Charts with Traditional Excel
|
Virtual on Zoom |
|
March 20, 2025 9:30 a.m. - 12:30 p.m. |
Power BI: Applying What You Learnt Already to Power BI
Other Challenges, More Advanced Tools and Techniques
Wrap Up and Next Steps
|
Virtual on Zoom |
Key Takeaways
By the end of the course, participants will learn:
- A method and design rules for creating the most effective, efficient, and reliable data models and reports/dashboards.
- How to connect to data sources (including databases and folders with multiple files) using Power Query for data extraction, transformation, and loading. You'll discover how to record a script to extract and transform the desired data, load it into the data model or other queries, and then automate the process to run it with just a click, eliminating manual work.
- How to design and build diverse data structures, from simple single tables to complex normalized multi-table models that ensure data integrity and ease of maintenance.
- How to create key metrics and measures using DAX, short code, and no-code approaches.
- When and why to use data visualization and dashboards, which visuals enhance interpretation, and how to structure dashboards for storytelling.
- How to apply your knowledge of Power Query and Power Pivot to build data models in Power BI Desktop.
- Advanced concepts and options, including; troubleshooting errors, potential for more complex developments using DAX, and strategies for tackling more complex models and problems.
Earn a Digital Badge
Our new collection of digital badges can be earned through the completion of any of our certificate programs offered in our professional development catalogue. Collect these digital badges to add to your website, social profiles, email signature and more. These badges are an easy way to visually communicate your skills and accomplishments to your peers and network. Digital badges will be awarded based on course completion requirements.
Who Will Benefit
This course benefits accounting professionals, controllers, and others looking to:
- Automate reporting from raw data for increased speed, reliability and accuracy.
- Build easily analyzable models across multiple dimensions (e.g., dates, regions, products, product features) using interactive tools to identify trends and answer business questions.
- Provide self-service solutions to business users.
- Present data in various formats, including dashboards and free-format Excel, while leveraging the Power Pivot data engine.
- Gain Power Query and Power Pivot skills applicable to Microsoft Power BI development.
Prerequisite(s)
The course is suitable for all experience levels and participants should expect to work on different skills and techniques than they may have used in traditional Excel, including some more technical concepts. These include data modeling structures, the ‘M’ language scripts generated by Power Query, and the potential use of ‘DAX’ for advanced modeling. The course will explore various approaches to building models with different technical complexities, addressing where advanced users may use coding and offering some alternative approaches to avoid the need for coding by everyday users.
Previous participants of the i3BAR reporting and i2VSA dashboarding courses, as well as other advanced Excel users, will find this course especially beneficial to enhance their existing skills.
Need Approval to Attend
Attendance Requirements
To earn your CPD hours you must attend all live sessions. We understand that sometimes unplanned absences happen. Please reach out to our Operations Team if you're unable to attend a session as scheduled. To earn a Digital Badge, you must meet the attendance requirements outlined above.
How to Access the Course
We're pleased to invite you to register for our Certificate courses. Whether or not you're a CPA, you're more than welcome to join us on this learning journey. To get started, you'll need to create an account. Don't worry if you don't have a CPA Ontario ID yet; you can easily set up your account right here. Let's begin your learning adventure together.
This CPA Ontario course is a live certificate program, delivered over four, half-day, sessions. We recommend you join five minutes prior to the scheduled starting time of each session. Recordings of all sessions will be available to all registrants.
To access the course please visit our BlackBoard site, and log-in using the same login and password used for the Registration Portal.
Please allow up to 15 minutes after registration for the certificate program to appear on your BlackBoard page.
Registration, cancellation, withdrawal and all other CPA Ontario PD policies can be found here.
Speaker(s)
Simon Lindley is an international trainer, speaker and consultant in business transformation through analytics. He brings expertise in data analytics, visualization and dashboard design to transform the role and performance of finance teams. Simon is a Chartered Accountant, Chartered Engineer (IT) and MBA with a Masters Diploma in corporate governance. He also has training and qualifications in statistics and data science. In addition to training Simon has worked as a project/ commercial manager for business and finance transformation projects for large corporations, Big 4 Accounting and Consulting firms and a major research business.