Power Pivot Principles: Tidying Data

Welcome back to our Power Pivot blog. Today, we discuss potential steps we can take when we receive ‘messy’ data.

Before we begin, you can download our ‘messy’ data sample here and work along.

Using this data, after importing our data into Power Pivot, it looks something like this:

There are columns that are entirely blank, such as the Suffix and AddressLine2 columns. Looking at the rows, some of them are completely blank too.

To eliminate blank rows and columns, we can filter the data before importing it to Power Pivot. If you forgot how to do this, head to the design tab on the Ribbon of the Power Pivot window, and select ‘Table Properties’.

We can then filter out the blank columns by choosing to not have them imported into the dataset:

To filter out the blank rows, we can choose a drop-down menu of one of the columns ‘Customer Name’ and unselect the ‘Blanks’ values.

Our dataset is now looking a lot better:

Looking closer, some entries in the Customer Name column have been entered incorrectly into the Customer Group column, together with a couple of errors in the Customer Group and Email Address columns.

We can fix these issues by ‘cleaning’ the data in Excel. Let’s utilise Power Pivot’s clipboard function. Select all (CTRL + A) and copy (CTRL + C) the table from Power Pivot and paste it (CTRL + V) into a sheet in Excel. Let’s perform a few more adjustments to the data, et voilà!

Obviously, it would make sense to perform the adjustments in Power Query or Power Pivot, but that’s not the point here…

Now how do we get this back into Power Pivot? First, convert the range into a Table. Highlight the range and press (CTRL + T), to convert it into a Table. Be sure to tick ‘My table has headers’.

Give the Table a name, then head up to the ‘Power Pivot’ tab on the Ribbon and select the ‘Add to Data Model’ option.

And there you have it, our data all cleaned up, back in Power Pivot.

Happy pivoting!

Stay 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.