• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Reformats Ugliest Report Ever

You are here: Home / Power Query / Power Query Reformats Ugliest Report Ever
Ugly Excel Report
February 9, 2016 by Mynda Treacy

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:

reformat reports with power query

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:

excel tabular report format

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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:
  2. power query 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.

  1. 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:
  2. power query filter data

  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. power query replace values

  5. Now I can replace the ‘null’ cells with the entitlement amounts. Select the Value column and on the Transform tab > Fill > Up:
  6. power query fill up

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

power query add index

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

  1. Now we need to use the Index column to add a Modulo column. With the Index column selected > Add Column tab > Standard > Modulo:
  2. power query add modulo column

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:

power query modulo column

  1. 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’:

power query pivot data

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

power query pivot results

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

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

  9. 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:
  10. rename query

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

excel tabular report format

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.

Ugly Excel Report

More Power Query Posts

power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
Category: Power Query
Previous Post:Excel Power QueryPower Query Version Compatibility and Installation
Next Post:Excel Dynamic Text LabelsExcel dynamic text labels

Reader Interactions

Comments

  1. Rudra Sharma

    February 12, 2020 at 11:28 pm

    Thanks for sharing this. I tried without looking at the steps shown by you and happy that the output matched with that of yours.
    Here is my code.

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    #”Changed Type” = Table.TransformColumnTypes(Source,{{“Entitlements”, type text}, {“Value”, type any}}),
    #”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([Entitlements] null and [Entitlements] “Total:”)),
    #”Added Custom” = Table.AddColumn(#”Filtered Rows”, “Custom”, each if [Value] = “*None” then [Entitlements] else null),
    #”Filled Down” = Table.FillDown(#”Added Custom”,{“Custom”}),
    #”Filtered Rows1″ = Table.SelectRows(#”Filled Down”, each ([Value] null and [Value] 0 and [Value] “*None”)),
    #”Reordered Columns” = Table.ReorderColumns(#”Filtered Rows1″,{“Custom”, “Entitlements”, “Value”}),
    #”Changed Type1″ = Table.TransformColumnTypes(#”Reordered Columns”,{{“Value”, type number}})
    in
    #”Changed Type1″

    Reply
    • Mynda Treacy

      February 13, 2020 at 8:48 am

      Congratulations, Rudra! 🙂

      Reply
  2. Sarah

    January 14, 2019 at 11:16 am

    Hi,

    I have tried this using an entitlement report from my company’s MYOB file. The problem is, some employees have a balance for RDO’s and some don’t. MYOB won’t show a balance of $0 – it only lists the entitlement type if there is a balance. Therefore, when I get to step 6, the Modulo Index won’t work as there is not an identical number of records for each employee. Is there an additional step I can take to resolve this?

    Thank you in advance,
    Sarah

    Reply
    • Catalin Bombea

      January 14, 2019 at 3:31 pm

      Hi Sarah,
      Can you upload a sample file on our forum? It will be easier to find a solution for you. Create a new topic after sign-up to upload the file.
      Regards,
      Catalin

      Reply
  3. Bundu Dumbuya

    February 18, 2016 at 7:43 am

    Hi Mynda

    Thank you for the clear illustration. It was very helpful.
    I took your Power Pivot and Dashboard courses. I learned skills and techniques that are proving very useful to me now.

    I would like to know which program you used to record your material and illustration. I am mainly interested in know what you used to highlight sections in your presentation while explaining. That made understanding your explanations very easy.
    I am always conducting presentations at work and would like to know how to make them more effective.

    Thank you very much.

    Bundu

    Reply
    • Mynda Treacy

      February 18, 2016 at 10:05 am

      Hi Bundu,

      Great to hear you enjoyed my courses 🙂

      I use Camtasia Studio by Techsmith to record my videos.

      Kind regards,

      Mynda

      Reply
      • Bundu Dumbuya

        February 20, 2016 at 10:28 am

        Thank you very much

        Reply
  4. Heather

    February 17, 2016 at 7:47 am

    Wow. I had to download Power Query. I wonder what other features I am missing.

    Reply
    • Mynda Treacy

      February 17, 2016 at 9:07 am

      Glad you like it, Heather. Chec kout Power Pivot next.

      Mynda

      Reply
  5. Glenn

    February 12, 2016 at 3:19 am

    Mynda:

    Step six appears to have a typo, Modulo value s/b 2 , not 3.

    Thanks for demonstrating the capabilities of Power Query. I have coworkers with 2007 who are quite jealous…

    Reply
    • Mynda Treacy

      February 12, 2016 at 9:50 am

      Thanks for spotting my typo, Glenn. All fixed now.

      Power Query makes upgrading from Excel 2007 completely worthwhile. Let’s hope your co-workers can get it soon too.

      Reply
  6. maggie pont

    February 10, 2016 at 9:49 am

    This is so fantastic, wonderful video as always.

    Could you show how when the new figures come do you apply the query to them.

    Kind regards
    Marg Pont

    Reply
    • Mynda Treacy

      February 10, 2016 at 10:53 am

      Hi Maggie,

      Glad you enjoyed it.

      I cover how to re-use queries in my Power Query course, but one option is to save a copy of the file and put your new data in the Excel Table that you linked to Power Query and simply Refresh.

      Another is to open the Advanced Editor in the Power Query window > copy the M code and paste it into your new file, but that’s more complicated and is covered in a video in my course.

      Kind regards,

      Mynda

      Reply
  7. MAKSIM ZELENSKIY

    February 9, 2016 at 9:33 pm

    Some more ugly reports reformatted with Power Query:
    http://excel-inside.pro/blog/2015/11/12/using-the-header-of-the-report-as-the-data-for-table-columns-in-power-query/
    And
    http://excel-inside.pro/blog/2015/11/09/transfer-values-to-other-columns-in-power-query/

    Reply
    • Mynda Treacy

      February 9, 2016 at 9:42 pm

      🙂 thanks for sharing, Maksim.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x