Automatic report cleanup
You may find yourself saddled with reports that you need to analyze in Excel that are particularly unfriendly from an analytical standpoint. You may even be manually cleaning up such reports monthly. Or perhaps you’re writing macros in Excel to automate repetitive tasks such at this. I will tell you that I write far fewer macros these days now that Power Query is available.
As we saw in the workbook index example, Power Query offers a code-free approach that is designed to be “set-and-forget.” We're going to transform a January accounting report into an analysis ready format, and then save a February report over it. This will illustrate how once you establish Power Query, the only repetitive step is to run the new report and save over the previous version of the data source.
First, let’s look at some common frustrations that arise in reports that you export to Excel from other software programs.
