Power BI Solves - The Data Transformation Problem - The Finance Department Training Company

The Data Transformation Problem

The first in our series of Power BI Solves, a collection of articles demonstrating common business issues that Power BI can solve (of which a fuller list can be found here), this article covers the Data Transformation Problem. I’ve started our Power BI Solves series with this problem as it’s the main reason why I learned Power BI nearly 9 years ago!

Back in 2017, I worked for a UK retailer and we did everything in Excel, from budgeting to invoice checking. To carry out these tasks we relied on data passed to us in Excel from a lot of different systems. We didn’t control much of the data and it often came in an unhelpful format. Every time we needed the files for a piece of work, we needed to do a lot of transformational work to get the Excel data in a position that we could easily use it.

Such transformational work might include:

removing columns or rows,
adding in columns or rows,
splitting data,
looking up information from other datasets to add extra dimensions,
extracting date information
and a lot of others steps!

We needed to do this for lots of different repetitive reports or tasks EVERY DAY (or every week/month/year etc). Some of these transformational tasks took 15 minutes, others took closer to an hour each time we had to perform them.

This data transformation HAD to happen before we got onto the fun job of the task that we were actually meant to be carrying out (the analysis, budgeting, forecasting reporting and so on). This meant that we then had less time to do the actual work that was adding value to the company. A 2017 benchmarking report carried out by PwC Australia highlighted that the best finance functions spend 20% more time analysing data than they do collecting it. Well. after nearly a decade in audit and finance functions at that point, I hadn’t seen many of those PwC defined ‘Best Finance Functions’!

Power BI completely solves this problem by automating the transformational work.

Using Power Query to connect to and transform a dataset, you teach Power BI how to perform the transformational steps. You do this through a really easy user interface – we’re talking click a button on a ribbon. Power BI then writes down the transformational steps and their order and remembers them. Each time you have an updated dataset that you’d like your report to use, simply save it down and click a ‘Refresh’ button in Power BI and Power BI will perform all the necessary steps to do the transformation for you. And to add insult to injury, it won’t make mistakes and will almost certainly do it much faster than you can.

But this is good, because you’ve saved time by getting the machine to do the repetitive work leaving you more time to do the interesting task you were trying to do to start with. So you know, everyone’s a winner.

Video Chapters

00:43 – What is the Data Transformation Problem?
01:22 – An example of the Data Transformation Problem
02:37 – How can Power BI solve this problem?

Short On Time?

The Problem

To create reports in Excel such as analysis, reconciliations, journals, budgeting and so on, you need some data to start with. That data is often not in great shape and needs some transformational work, so that it can be used in PivotTables or static data tables.

In our example, our fictitious company is trying to create a daily Sales presentation in Powerpoint, but need Excel datasets to complete the report. These datasets first need transforming like removing columns, rows, adding in columns, looking up data, splitting data, extracting dates and more before we can use the data to create the report.

The transformation takes 15 minutes to perform, but across a year this is around 65 hours of time spent performing simple repetitive tasks.

How Power BI Solves the Problem

Power BI uses software called Power Query to connect to and transform data held in hundreds of different places, including Excel files held in traditional storage systems such as Sharepoint, Shared Drives, hard drives, OneDrive and so on.

Once you’ve connected Power BI to your datasets, then you can transform how the datasets looks in Power BI, through a series of commands called steps. The commands or steps are very easy to give to Power Query, through clicking a series of buttons on Power Query’s ribbon. Power Query then creates a list of these steps to go through each time you present Power BI with that dataset, or an updated version of the dataset. Once your transformation is complete, you can create your report from the transformed dataset.

To update the Power BI Report, simply save an updated version of your datasets or add new files to the same folder in which other connected files are stored. Once new files are saved down, click the Refresh button on the Power BI Report View ribbon. Power BI will then run through the necessary transformations (because of course it has a list of them!) and update the file, faster and more accurately than you can!

…and a little more

Because Power BI saves a list of the dataset transformations, you can guarantee that you will get transformational result every time you refresh. This may be an advantage over other transformation attempts, for example using AI.