Why should Finance Teams especially love PowerQuery?

Private: PowerQuery for Finance Teams Why should Finance Teams especially love PowerQuery?

Why should Finance Teams especially love PowerQuery?

Previous Session Summary

In the previous lesson, we looked at what PowerQuery is and how it works, including the 5 stages of PowerQuery.

The 5 stages include a) Connect to your dataset b) Transform your dataset c) Load your dataset for interrogation d) Interrogate your datset and e) Add more data.

Lesson Synopsis

In this lesson we look at why PowerQuery is a cheap and easy to use tool to make every Finance Team far more efficient. Why? PowerQuery automates the cleaning, joining and formatting of information and Finance Teams do a lot of this to do their every day tasks.

In this lesson we look at the standard data cleaning and formatting procedures that most Finance Teams have to go through just to perform their necessary tasks such as journal posting, analysis and reconciliations. We learn that PowerQuery can automate these procedures so that they are done very quickly and with no chance of error.

It’s also easy to learn and use as the PowerQuery Editor has a great dashboard with buttons for the most used commands. It’s then a simple case of clicking the buttons you need to transform your dataset.

This leaves Finance Teams much more time to get on with the tasks that they have to do.

Resource Listing

The Video

Play Video

Lesson Notes

If we think about it, more or less every task that a Finance Team performs is repetitive, by which we mean that it will be performed every year, every quarter, month, week or even day. That includes:

Internal Report production
Budgeting and forecasting
Any form of analysis
Balance sheet reconciliations
Journal posting
Financial Statement Production
Invoice validation
Payroll

All of these tasks need information whether it’s from an accounting system, a third party, another department or somewhere else. The information then needs to be taken from its ‘dirty’ state, where it might have spare columns, rows of information we don’t need, non columnar formats, and so on and put into a ‘clean’ state so that we can use the information to perform the task that we need to do.

Cleaning the information manually can take some time. It might be quite quick, like simply copying and pasting the information in a report into a pre-existing report. It could also be quite laborious, using VLOOKUPS to take information from one file and putting it in another, segregating information to only get the data that we need, using functions on pre-existing columns and so on.

Regardless of whether the transformation is quick or slow, it needs to be done every time we need to do the task. That takes time out of our day that we could be using to actually do the task plus we run the risk of making mistakes.

PowerQuery automates the cleaning and transforming process so Finance Departments only have to do the transformation of the datasets once. PowerQuery records the transformations that need to happen like removing rows, and then the next time a new dataset comes in, it applies the transformations it recorded to that new dataset.

Imagine the time saving in your Finance Team!

.... and a little more!

Get round the Excel data limit – Excel has a row limit of 1,048,576 rows. Any datasets that are larger than this can’t be stored in a single Excel spreadsheet and that causes problems for trying to do any sort of work in pivot tables. PowerQuery is built to deal with millions of rows and to combine datasets from multiple tabs and spreadsheets together. The resulting report may be more than the 1,048,576 row Excel limit, but PowerQuery can load the information straight into a Pivot table, bypassing the Excel row limit.

Quick performance – Have you ever sat for an hour waiting for a large Excel file to open (and then worry whether you’ll be able to do any work in it before it crashes?) Because PowerQuery is built to deal with millions of rows, even large files with lots of calculations can be easily replicated in PowerQuery and will open in seconds. Additional calculations are also quick to perform making the file much more reliable to use at crunch times like period end.

Previous Lesson

Back to Course

Next Lesson