Power Query: Inquiring Minds

Welcome to our Power Query blog. In a plot twist, today’s blog is about how Excel can make Power Query data easier to handle.

(N.B. At the time of writing, the Inquire add-in is only available in the Office Professional Plus and Office 365 Professional Plus editions of Excel.)

I have some employee data that I want to extract to Power Query:

I go to the ‘Data’ tab and choose the ‘From Table’ option in the ‘Get & Transform’ section.

Although my worksheet looks okay, I find I have some trailing rows to deal with. I can handle this by using the ‘Remove Rows’ feature on the ‘Reduce Rows’ section of the ‘Home’ tab, but I really want to reduce the amount of time spent extracting this table and just concentrate on the other tasks I need the data for.

There is an Excel add-in I can use. To see the add-ins available, I can choose the ‘File’ tab from my Excel worksheet.

I access the ‘Options’ tab.

On the ‘Excel Options’ screen, there is a tab called ‘Add-ins’:

In my ‘Inactive Application Add-ins’, I can see an available add-in called ‘Inquire’. This is marked as a ‘COM Add-in’. In the ‘Manage’ drop down, I choose to look at the ‘COM Add-ins’.

I don’t currently have ‘Inquire’ activated, so I click in the box next to it, and then ‘OK’.

A new tab called ‘Inquire’ has appeared between my ‘Power Pivot’ and Design tabs.

One of the options I have on my new tab is to ‘Clean Excess Formatting’. This will remove any formatting I have on cells which no longer contain data.

I have the option to clean just my active sheet, or the default, which cleans all sheets in my workbook.

Having cleaned my workbook, I am prompted to save any changes. It should be noted that Microsoft recommend saving a copy of your data before you apply this change, since there are some cases where the file size increases (usually it’s the opposite!).

Now when I extract my data, the extra rows no longer exist. This technique is useful for large data sets in old worksheets which may have been edited heavily over a period of time. If cells exist with no data, but still retain their formatting, then Power Query could pick them up as active cells. Using ‘Clean Excess Formatting’ can reduce the data extracted, and the cleaning needed for this type of data.

Come back next time for more ways to use Power Query!