Power Query & Power Pivot Course: Automating Reporting

Aim of course – to teach fundamental theory and knowledge of Power Query and Power Pivot, applying this to create number-focused financial reports relevant toaccountants, such as a Profit & Loss and Sales Dashboard, to support and automate their monthly reporting and analysis.

Monthly Reporting Context – each section of the course begins by framing the learning in the context of the problems and challenges faced in traditional financial reporting approaches and the solutions Power Query and Power Pivot provide.

The Company – the course is based around a fictional gym equipment wholesaler, Workout Warehouse - a simple and relatable business that is created by, and exclusive to, Claritix.

Course data – is comprehensive and realistic and comprises of actuals, budget, forecasts and prior year data. It includes account codes and cost centres to add realism and relevance to the audience, as well as product and customer characteristics to allow for more commercial analysis and insights. There is also a large data set to showcase Power Query and Power Pivot’s ability to leverage large quantities of data.

Course resources – aside from data, there is a downloadable 63-page slide deck that the course is built around, Excel files capturing various stages of course progression, and other support materials.

Report build & quizzes – you are encouraged to put into practice what you’ve learnt by following along with the report build as you go through the course and test your knowledge with regular quizzes.

Intro to Power Query – overview of the tool used to connect to data sources and automate the transformation and clean-up of data.

Importing data & automating transformations – the steps taken to connect to data, make edits or transformations to it in the Power Query Editor, that will be automatically re-run in future, before finally importing it into Power Pivot.

Creating a date table with a blank query – a brief introduction to M code, the programming language used by Power Query, and how it can be applied in a blank query to create a table of data to later be used in Power Pivot.

Adding a new month of data – demonstrations of different ways to add a new month of financial data to your reporting i.e. to regularly update your reports.

Data model theory – a look at what a data model actually is, its benefits, and the characteristics of the tables involved – lookup vs fact tables, primary vs foreign keys etc.

Creating table relationships – demonstration of how to construct a data model in Power Pivot to rapidly and robustly map data.

Filter flow – theory and explanation of how filtering works in a data model.

DAX operators, calculated columns & measures – an intro to DAX, the formula language of Power Pivot. We look at the role played by DAX in calculating our reports, examine the syntax, and start experimenting with a few functions.

Measure tables – a great technique we use to keep the measures we create organised and easily retrievable.

DAX techniques – throughout the DAX learning we’ll be looking at tips and techniques such as formatting our code, optimisation, and a technique known as ‘measuring branching’.

Most useful functions – with over 250 DAX functions to choose from, the slide deck contains a helpful quick overview of the key ones. Some will be used in the course whilst others are for your own experimentation as you grow in confidence.

Profit & Loss – we create a Profit & Loss financial statement using special CUBE functions in Excel that filter the data model and return aggregated results. We present actuals against a chosen comparator - Budget, Forecast or Prior Year - along with variances. Information is presented by Period, QTD and YTD and we can filter for the month in question.

Sales Dashboard – we create a dashboard that utilises a multitude of filters, relating to products, customers, geography, and data, that returns and analyses our data-heavy product sales information.

PivotTables – we look at how to create PivotTables that connect directly to our data model, leveraging both the data and our DAX calculations, and allowing us to rapidly slice, dice and explore the results.

Actuals vs Budget/Forecast – given our financial reporting rarely consists of just actuals, but rather compares them to a budget, forecast or prior year, we look at how we can bring these into our reports and create variances to the actuals.

Period, QTD, YTD – a look at how to calculate and present financials for a single period, Quarter-to-Date and Year-to-Date, allowing us to present a full and comprehensive view of data.