Do you want to progress beyond traditional Excel modelling to using Power Query and Power Pivot in Excel and/or Power BI? Learn to build reliable, interactive reports and dashboards that automate the extraction, transformation, loading, and analysis of data from multiple large and diverse data sources with just a click.

CPAs face increasing demands for faster reporting and for rapid and flexible analysis of large and diverse data sets. Time-consuming and error-prone traditional Excel models with simple tabular data structures are no longer suitable to meet many of these requirements.  Learning Microsoft Power Query and Power Pivot (included in both Excel and Power BI) will enable rapid development, automation of updating reports/ analysis with new data, better structured and scalable data storage and interactive analysis.

This saves CPAs valuable time and effort, reduces the risk of manual processing errors and can ensure more reliable and timely reporting and analysis.  Interactivity and drill down into the data enables rapid investigation and analysis to answer queries and resolve issues and enables the production of ‘self service’ models for users if required.

The course will cover how to use the structured data model not just for tabular pivot reports and dashboards, but also how to extract and build free-format report layouts in Excel.  This enables users to combine the power of the same data engine as used in Power BI with the free format reporting flexibility of traditional Excel.

The course will be taught around a hands-on case study starting from raw data and will cover a method and approach for users to apply what they learn to developing their own models. 

Program Schedule

• Aug 11, 8:30am-12:00pm
  Data analytics transformation
  Getting data: extract, transform and load with Power Query

• Aug 12, 8:30am-12:00pm
  Data modelling: building data models with Power Pivot

• Aug 13, 8:30am-12:00pm
  Data visualisation: reporting, exploration and analysis using Pivot charts and dashboards
  Flexible hybrid models: combining Power Pivot data and charts with traditional Excel

• Aug 14, 8:30am-12:00pm
  Power BI: applying what you learnt already to Power BI
  Next steps

To earn the Data Analytics Certificate, you must attend all the live sessions.

For more details, please see the Data Analytics Certificate Program page.

This course is relevant for all CPAs and accounting professionals who want to build models for reporting/ analysis, particularly if they want to

• Automate processing to remove the time and risks of manual processes
• Connect to large/diverse data sets that may need to be combined/ transformed before they can be used
• Enable interactive exploration and analysis of tabular data, visual data/ dashboards and free format layouts
• Progress their skills in Excel and apply this to development in Power BI

• Apply a method and design rules to create effective, efficient, and reliable data models, reports and dashboards
• Connect to data sources (including databases and folders with multiple files) using Power Query for data extraction, transformation, and loading. 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
• Design and build diverse data structures, from simple single tables to complex normalized multi-table models that ensure data integrity and ease of maintenance. Create key metrics and measures including some examples using DAX coding
• Use data visualization and dashboards to enhance interpretation and insights, and structure dashboards for storytelling
• Create free format reports in Excel using the Power Pivot data engine that are not limited to pivot table or pivot chart layouts
• Apply the knowledge of Power Query and Power Pivot to build data models in Power BI Desktop
• Troubleshoot common problems, reuse data queries in other models and develop strategies for tackling more complex models and problems

Live webinars have varying levels of expected interaction, with some requiring microphone and camera capabilities to support a proper learning experience – please email pdreg@bccpa.ca if you have concerns. 

In general, we encourage participants to have their cameras turned on to enhance their virtual learning experience. This practice promotes a stronger connection with the instructor and fellow participants, and fosters improved communication and collaboration.

Live Webinar FAQs can be found here.  

Registration terms and conditions, including the cancellation policy can be found here. If you require further assistance, please contact the PD Department.