Power Pivot Principles: Data Cleansing

Welcome back to the Power Pivot Principles blog. This week, we’ll talk about some of the inabilities of Power Pivot when it comes to organising data in a meaningful manner.

It has been a few weeks since we have been working on creating a Budget vs Actuals table of comparisons for company XYZ. Let me refresh your memory by bringing in last week’s result, viz.

I am sure most of you are wondering what’s next, as we have almost achieved our comparison goal. However, before we draw any conclusions, I would like to point out that the data we started to work on was “clean”. From the very first blog of this series, where we summarised the data, we had a Budget table, a Transaction table, and a Charts of Accounts table to work on.

This week I want to highlight some of the limitations that Power Pivot has. Despite being a regular user of the tool, Power Pivot is not my “go to tool” when the data needs organising or cleansing before I start any analysis.

Let me explain by giving an example. For instance, if I would like to delete a row in the Transaction table, I cannot do that. I could ‘Copy’. The other way is to make changes in the source data which is never a good idea.

When the data needs to be organised like below, Power Pivot does not offer many options:

This is where Power Query comes in handy. To give you an idea, let me show you some of its abilities to transform and cleanse data, e.g.

Power Query offers to clean your data before you perform any analysis. But don’t worry; we have a blog series on Power Query. If you haven’t already, feel free to take a look at our sister series now.

That’s it for this week!


Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.