Let’s say we just got handed a report in Excel and have been asked to calculate various things from it. And the report looks something like the image below. Now, there are two ways to go about it:
As data analysts, we are very comfortable with the first method, so much so that it appears boring and a waste of time. The second method, however, looks like something we’d be very interested in, especially if it automates this process for us. And our very own Oz du Soleil is here to show us this trick of the trade.
There are a few properties our scattered report data should have:
With a few tries on this method, any of us would be able to see that this consistency requirement is actually not as strict as it appears.
This is actually a fun part. We need to manipulate the data Power Query has grabbed to make it look like a table we would want to work with. Here’s what we’ll do with the data:
Once the cleaning has been done, all we need to do is press ‘Close & Load’ on the upper-left side of the Query Editor. And there we have it, the big, ugly wall of data we all are familiar with.
There’s another thing that needs to be highlighted. This wall is “dynamic”! What do we mean by that? Well, we wouldn’t want to do all of this again if new data is added to the report sheet. Especially when there are many formatting steps, not just three as in our example.
But there is no need to worry. When you would paste new data, click on any cell in this table just refresh it. Excel will automatically pick the new data up with the desired formatting! Check out the result in the image below.
Give it a shot! See if this eases up your life. And your experiences with us below.
Do not forget to tell your fellows about this tip. Let them save a lot of their time too.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.