Power Query Reformats Ugliest Report Ever

In this tutorial we're going to reformat Excel reports with Power Query.

Why? Because external systems can create some pretty ugly reports. Here’s one from a popular Australian accounting system, MYOB:

It’s not just ugly because of how it looks. It’s also ugly because the layout prevents you from (easily) doing any further analysis of the data.

According to Excel legend the most common button in external reporting systems is ‘Export to Excel’.

Unfortunately 99% of these external systems spew out useless formatted reports like the one above, when what we really need is the data in a Tabular format.

What’s an Excel Geek to do?

You could spend some time cutting/pasting/transposing etc. to reformat the report into a Tabular format, but doing this every month gets pretty tedious.

You could write some VBA to automatically reformat the report for you. But then you have to know how to write VBA to do that.

My preference is to use Power Query to transform the report into this super useful Tabular layout like this:

Once it’s in a Tabular format you can easily use Excel’s tools to analyse the data. Tools like PivotTables, SUMIF(S), COUNTIF(S) and other aggregate functions to summarise the data.

Not sure if you have Power Query in your version of Excel? Here is a list of which versions of Excel include Power Query.

Download the Workbook

Watch the Video

Reformat Excel Reports with Power Query

There are several steps to transform the ugly duckling MYOB report into a super useful Excel swan, but they’re easy (unlike VBA). It took me 1 minute 30 seconds to complete the following steps.

And even better, once you create those steps you can use it again next month with the click of the Refresh button.

1. Select the data you want to work with, in my case it’s in cells B9:C221. Go to the Power Query tab (or Data Tab for Excel 2016), From Table:

Note: if your data isn’t formatted in an Excel Table then it will do this for you and ask you if it has headers before loading it into Power Query.

2. We need to start culling parts of the report that we don’t need. I’ll use the column filters (they work just like in Excel), to remove the empty and ‘Total:’ rows from the Entitlements column and from the Value column I’ll remove all of the zero values:



3. I need to replace *None in the Value column with null. Null in Power Query is the equivalent to an empty cell. To do this select the Value column > Home tab > Transform group: Replace Values:



4. Now I can replace the ‘null’ cells with the entitlement amounts. Select the Value column and on the Transform tab > Fill > Up:



5. We need to split the Entitlements column into one for the name and one for the entitlements. We use some Power Query magic dust to do this.

On the Add Column tab > Add Index Column (starting at zero is fine).

You’ll end up with a new column called Index which simply numbers each row starting at zero:

6. Now we need to use the Index column to add a Modulo column. With the Index column selected > Add Column tab > Standard > Modulo:

At the Modulo dialog box enter 2, which is the Index number for the second record (name) in the Entitlements column.

It should look like the image below, with a zero for the rows containing names and a 1 for the type of entitlement:

7. Now we can use the Modulo column to pivot the data and split the Name and Entitlement into two columns.

Select the ‘Inserted Modulo’ column > Transform tab > Pivot. In the Pivot Column dialog box choose Entitlements as the values column and under ‘Advanced Options’ choose ‘Don’t Aggregate’:

Now we have the names and entitlement types split into two separate columns (named 0 and 1):

8. All we need to do now is use the Fill Up tool we used earlier to replace the null values in column 1 (Entitlements).
9. Next filter out the rows containing null from column 0 (Names).
10. Delete the Index column (select and press the DELETE key). Its work is done.
11. Drag and drop the columns to rearrange their order so Value is the third column.
12. Rename columns 0 and 1: double click the column headers and type in new names:



13. For bonus points let’s sort by the Name column. Select the Name column > click on the Filter button > Sort Ascending:



14. Before we load the data into Excel let’s give the query a better name. In the Query Settings: Properties type a new name in the Name field:

Now you’re ready to Close and Load your data into a new, nicely formatted Excel Table:

Don't be put off by the number of steps involved. Remember it only took me 1 minute and 30 seconds to complete the steps above and next month you can reuse the query, so it'll take you a few seconds to press the Refresh button to clean and transform the new report.

Thanks

Thank you to Cathy Benson for sharing her ugly MYOB report with me. And thank you to Excel MVP's, Ken Puls and Miguel Escobar, for teaching me the Modulo trick.