Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…
To make matters interesting each sheet has a different format.
What now?
Of course Power Query to the rescue.
This is an advanced example of Power Query. If you are a beginner, start with these pages.
Combine multiple Excel files – the problem
Imagine you work in Finance. Your job involves paying employees for their business travel expenses. Every time someone goes on a business trip, they submit a trip expense report. This is an Excel template with two tabs.
- Travel details tab: for gather personal and travel details
- Expense details tab: for itemized expense details
As you have a lot of employees, you don’t want to manually scan the files and combine the data. Here is a sample of how these files look.
You want to combine all the expense files in to one big, consolidated & refreshable travel expense workbook.
Using Power Query to combine files
Some of you may already know Power Query’s “Get data from Folder” feature. This helps us easily get & combine multiple excel files in a folder. Unfortunately, this alone will not be helpful for us as our file has two different tabs and we need to combine them separately 😉
Here is the process we need to follow.
Start by placing all the expense reports in to one folder. This can be a folder on your computer or on a network / shared drive.
Now go to “Get Data > From File > Folder”
Point to the folder path and Power Query will show all the files in that folder.
Once satisfied with the list of files (don’t worry if you need to exclude some files, you can do that while editing the query by applying filters), click on “Combine & Edit”.
Now you will get another screen asking you choose which tabs / tables you want to bring. As we have two sets of consolidations, let’s start with the first one – travel details tab. Select that and proceed.
At this point, Power Query will create a folder called “Transform sample” and places a few things in it. PQ will also create a query for all the merged data. This is how your Power Query window could look.
Editing the Transform sample query
As you can see, the default combined query data can be useless for our situation. So let’s proceed by editing “Transform sample file from reports” query.
What is Transform sample really?
In this sample query, you can make any changes and PQ will apply them to all the files in the folder before combining them to one gain data set.
Steps to turn travel details to a table
Our travel details sample needs to become one row table so that we can effectively merge multiple files. To do so, follow these steps:
- Remove blank / heading rows on the top.
- Remove any nulls or unnecessary rows from column 1
- Transpose the table
- Promote first row to headers
This is how the output would look after the process.
Combine all files
Now that we have edited transform sample, time to go back to the “reports” query to see the output. If you are happy with it, rename the query and load it in to Excel (or Power BI).
Combined travel details
Combining expense details
The process is same for expense details consolidation. Start by creating a fresh “from folder” query. As expense details are in a table, there is no need to do any additional changes to the transform sample. Simply combine everything from “expenses” tables and you are done.
Combined expense details
Download sample files to practice this
Power Query can be tricky to explain with blog posts alone. That is why I made few sample files and consolidated workbook. Click here to download everything.
Try to merge the files in “reports” folder using your own logic / transformation steps. Share your story / tips in the comments.
I get an error when merging data from files
There are many reasons why Power Query may show an error when connecting to a folder. Here is a check list to help you.
- Make sure the folder path is valid and accessible. If you created the query on one computer and try to refresh it from another, chances are it won’t work. Use shared network drives or change path in Power Query steps before refreshing.
- Files are loaded, but merged query errors. This can happen if you edited the transform sample. Usually Power Query adds “Changed type” steps automatically after you do something. These changed type steps refer to column names in the query and change data types. If you edit the transform sample and alter the column structure of table, then the query will fail. The solution? Simple, delete all the automatically added changed type steps.
- Some files should not be loaded, but they load and mess up the results. Before making any transformations, set up filters based on file type or names. This way you can prevent loading unnecessary files.
Do you merge / combine files with Power Query?
I do this all the time. My recent win was to merge 24 PDF credit card statements (2 types of cards over last 12 months) to one big table of data so that I can see trends and find out where I spend most.
What is your experience with combine multiple Excel files / folder query feature? What are some of your favorite tricks with this? Please post them in the comments section.
This article is inspired from a comment by Sourav.
18 Responses to “Best Charts to Compare Actual Values with Targets – What is your take?”
Great post. I can't vote, though, because the answer I want to put down is "it depends". As with all visualisations, you've got to take into account your audience, your purpose, technical skills, where it will be viewed, etc.
I'm with Andy: It depends. Some I would use, some I might use, some I won't touch with a barge pole.
Naturally I have comments 🙂
The dial gauge, though familiar, is less easy to read than a linear type of chart (thermometer or bullet). It's really no better than the traffic lights, because all it can really tell you is which category the point falls in: red, yellow, or green.
By the same token, pie charts are so familiar, people don't know they can't read them. Remember how long it takes kids to learn to read an analog clock?
Bullet charts don't show trends.
With any of the charts that have a filled component and a marker or ine component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
[...] Best Charts to Compare Actual values with Targets (or Budgets … [...]
I voted for #6 even though I agree with the other comments that it depends.
The majority of the votes are for the #2, thermometer chart. I still have yet to understand what happens when you are above plan/goal, which was brought up in yesterday's post.
Also, I agree with Jon in that it would be better to flip the series and make the filled part the target or goal and the line or marker the actual.
I am also a fan of using text when appropriate if the data is among other metrics in a type of dashboard. Calling it out by saying actual and % achievement is a good option.
Another "it depends" vote. Are you just looking at one or are you comparing a number of targets with actuals? You didn't include a text box. The problem with sentences is that they can get lost in a page of gray text. A text box can call attention to the numbers and line them up effectively.
I'm with Jon: "Some I would use, some I might use, some I won’t touch with a barge pole" and I'm surprised that some of your readers voted for the last group.
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
"It depends" is also my take. What I usually try to drill into my clients dashboard design is the fu ndamental difference between spot results (am I on target for this month) and long term trends.. I always try to create 3 different set of graphs to represent real perormance:
- spot results vs objectives
- cumulative results vs objectives
- long-term trend (moving average) mostly) to see where we're going
[...] Best Charts to Compare Actual Values with Targets – What is your take? (tags: excel charts) [...]
[...] Related: Charting Principles, How to compare actual values with budgets [...]
[...] Excel Charting Alternatives to compare values [...]
Jon says:
With any of the charts that have a filled component and a marker or line component, it makes more sense to use the filled component (area/ column) for target, and the lines or markers for actual.
Why does this make more sense? I like 6 the way it is, although I would use a heavy dash for the plan/target marker.
I totally agree, Bob. I would normally favour a line for the target and a column for the actual, you can see quite easily then which columns break through the line, then.
[...] best charts to compare actual values with targets — den Status mal anders zeigen, z. B. als Tacho [...]
Thermometer charts: "Not appropriate when actual values exceed targets" - this is easily solved by making the "mercury" portion a different color from the border, then you can clearly see where the expected range ends and the actual values keep going.
People seem to knock gauges quite a bit in dashboarding, but trying to show comparison of realtime data between operating sites and targets for each site can easily be done with a bank of gauges that have the optimal operating points at 12 o'clock.
The human eye is great at pattern stripping, and any deviation of a gauge from the expected 12 position will quickly register with an operator and attract his attention. Using a colour background, or meter edge, will also indicate the sensitivity of a particular site.
[…] Best charts to compare actual with target values […]
[…] Best charts to compare actual with target values […]
[…] work laptop I have a favorites folder just dedicated to Excel charts. Its got things like “Best Charts to Compare Actuals vs Targets” and “Best charts to show progress“. I love me some charts […]
I am wondering how will the plotting work, for some of the targets which may have been achieved before time. E.g. for the month of Jul the target was 226 and the actual was 219. So the chart will show a deficit in meeting the target by 7 points but what if this 7 may have been completed earlier in month of June. So ideally it not a deficit.