It’s very common that you need to combine data from multiple worksheets in the same Excel workbook when you’re using Power BI or Power Query/Get&Transform in Excel. Indeed a lot of people have blogged about how to solve this problem, but none of the solutions I’ve found on the internet work in more complex scenarios when the data on each sheet needs some kind of transformation before it can be combined. I was asked to explain how to do this recently while teaching a Power BI class, so in this blog post I’m going to walk through a worked example and point out a few issues that might trip up even experienced Power BI users.
First of all, the source data. Let’s say you have an Excel workbook with four worksheets: Q1, Q2, Q3 and Q4. On each worksheet is some sales data for the three months in each quarter; for example the Q1 worksheet looks like this:
…the Q2 worksheet looks like this:
…and so on. The required output for Power BI should be a table that looks like this:
Now most of the blog posts that describe this problem, such as Ken Puls’s post here, assume each worksheet has a table with the same column names on it. If each sheet has the same columns, this means you can just connect to the Excel workbook and get a table containing the contents (Miguel Escobar has a great post describing how to do this here) and then click the Expand/Aggregate button:
However in this particular case it doesn’t solve the problem, because we get this:
Aha, you may say, we have to transform the data before we can combine it and so we need to create a function and call it for every worksheet – the technique I’ve already blogged about here. And yes, that is basically what needs to happen, but the devil’s in the detail.
Here’s the solution, step-by-step:
Step 1: Get a table with all the worksheets listed
In Power BI connect to your Excel file as normal, then in the Navigator pane right-click on the name of the Excel workbook and select Edit rather than selecting any of the individual worksheets:
The result will be a table that looks something like this:
If you need to, filter out any rows that do not contain “Sheet” in the Kind column and also filter out any worksheets that you don’t want to combine data from.
Step 2: Create your template query
Duplicate the query above and call the new query Template.
Now, in the Template query, select one of the worksheets to use to build the query whose logic will be applied to all the other worksheets, and filter the table above so it only contains the row for that worksheet. In this case I’m using the worksheet called Q1:
Then – and this is important – remove all the other columns in the table except the Data column:
Doing this changes the M code generated for the next thing you’ll do; removing all these columns changes the way the row is referenced (see the section on “The effect of primary keys” in this post) and makes sure the name of the worksheet won’t be hard-coded anywhere.
After that click the Table link inside the cell, and you’ll see the contents of the worksheet:
There will probably be a Changed Type step in the query that sets the data types for each of the columns, and you will need to delete it:
You can now perform any other transformations you need on this query, but you will need to avoid any transformations that generate M code referring to any columns on the original worksheet that aren’t present on other worksheets. Remember, these transformations will need to be applied to the other worksheets and they will fail if they refer to columns that aren’t present – this is why you had to delete the Changed Type step earlier, because it sets the types on the January, February and March columns, and you’ll probably need to delete any other Changed Type steps that are created elsewhere in the query. Open up the Advanced Editor and check the M code for the whole query just to be sure.
In this case all I need to do is unpivot the month columns by selecting the Product column and using the Unpivot Other Columns button on the Transform tab, and then renaming the columns appropriately:
Step 3: Create a function
Next you need to create a new parameter by clicking the Manage Parameters/New Parameter button, call the parameter Worksheet, set the data type to text and have it return the name of the worksheet you chose in the previous step:
Now, go back to the Template query, find the step called Filtered Rows towards the beginning where you filtered down to a single worksheet, and click the gear icon next to the step to edit it:
Then, edit the step so it uses the value returned by the parameter to filter by instead of the hard-coded value you entered earlier. To do this, click on the button shown below, select Parameter and then select the Worksheet parameter in the next dropdown box along:
Finally, go to the Queries pane on the left-hand side of the screen and right-click on the Template query and select Create Function..
You’ll be prompted to give the new function a name; call it GetData:
Step 4: Invoke the function and combine the data
Finally, go back to the duplicate copy of the original query created at the beginning of step 2. Then go to the Add Column tab on the ribbon and click the Invoke Custom Function button and invoke the GetData function, passing in the contents of the Name column to the function’s only parameter:
Last of all, click the Expand/Aggregate button on the new column and expand the nested tables:
After removing any unnecessary columns, you’ll see the data from all the worksheets combined into a single table as desired:
Don’t forget to set the data types on each column.
You can download the Excel workbook used in this post here and the sample Power BI Desktop file here.
Is it possible for you to include the example spreadsheet in your post or should we just re-create same to follow along?
I can’t believe I forgot to share the files – thanks for reminding me. The links are now at the bottom of the post.
Wow. Good stuff Chris. You bring it to another level. Thanks for sharing.
This can also be extended to doing transformations on multiple workbooks within a folder before appending them, which can be really helpful for models where the number of files that are given is not specified or changes over time.
In fact there’s no need to do all this when you have multiple workbooks in the same folder – the Folder datasource in Power BI does exactly the same thing as I describe here but automates the creation of the parameter and function etc
Hi Chris,
Thanks for sharing this important topic with us.
I need to adopt your code to workbook base. For example every month i need to add new excel file with 5 sheets to my project and every sheets column names are different with eachother.
I have used your code and i have did for one workbook. Also i tried for multiple workbook but i havr failed.
If you suggest a solution for multiple workbook, i will appreciate.
Kind regards.
GREAT !!!!
Thanks a lot.
One small (very small) comment, I think that
”
Then go to the Transform tab on the ribbon and click the Invoke Custom Function”
should be Add Column ribbon
Thanks for the great post!
small comment,
“Then go to the Transform tab on the ribbon and click the Invoke Custom Function” , sholdn’t it be Add Column ribbon?
Yes, thanks – I’ve made the correction
Thanks for the great post!
small comment,
Can you help me, i want to combine from Data From Multiple Worksheets In The Same Excel Workbook Using Power BI, the column multiple worksheets in the same, for example:
Col A: kind of fruits Col B: Price: Col C: Short name fruit
And i have three local, a local is a worksheet, a worksheet have 3 col A, B, C
And i want to combine them into a worksheet. Dont transfer col to row as the same your example.
can you help me, pls,
thanks so much
Sorry, I don’t understand what you want to do here – can you give me some more details please?
How would you do it to combine 2 files that that have a common field to “join” and output it as a new file with all the information I want on it.. one report has a field the other report does not have but both have a common field (transaction id) as it were.
I am new to PowerBI so this is interesting stuff.
Thanks!
Does the Merge button in the Power Query Editor window do what you want? See https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9 for example
Thank you Chris.
Truly a great post. Really useful information here, thank you for that!
Awesome!!! thanks a lot!
@Chris, I can’t seem to refresh on PowerBI.com this kind of dynamic functions?
Getting the “references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
Hmm, yes – I can see that might be a problem. It should be possible to rewrite the M code to do this, as I describe here: https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-formula-firewall-error/ When I have a moment I’ll see what I can do to fix this and update the post.
Hi Chris,
Thanks a lot for this post, I learnt a lot. I am trying to extend what you have done to multiple workbooks e.g. sales data in different workbooks (different countries) each of which has a worksheet for each quarter, but so far i have not been successful. The goal is to have all the data in one table. I was wondering if you could elaborate on how to do this. Thanks again for a great post.
Hi! I am using your steps for my own data and ran into an error problem. I was able to follow along with your steps until the end of Step 3 and beginning of Step 4. It’s after you ask us to create a function. After the “Create a function” window pops up, and I fill it with what you’ve suggested, and then hit ok—a new screen pops up in the main body of the power query editor. In this new screen, it states “Enter Parameter” and then has a blank box (there in it states “Example: abc”), under which are the two buttons “Invoke” and “Clear.” I ignored this screen, and did not enter anything, but when I went to the duplicate query called “Template”–and when I tried to add an “invoke custom function” I get a bunch of “Error” in my GetData column.
So, it makes me wonder if I messed up that mini step of the “Enter Parameter” scream. If so, what should I have done? If I did the right action, do you have a thought as to why I did not get tables in my GetData column but instead “error?”
It’s difficult to say. What error message do you see when you click on one of the error values?
I got to the Invoke Custom Condition and I couldn’t find the Name Column
Hello, thank you a lot for your post. I have just started to use Power BI desktop som days ago. I want to know if you can give me some orientation in my work. I have two worksheets to combine and each of them contains at least two or more sheets.
The final result of the worksheet = worksheet1 (sheet1.1, sheet1.2,sheet1.3) + worksheet2 (sheet2.1, sheet2.2, sheet2.3) is supposed to be like this worksheet (sheet1, sheet2, sheet3) with sheet1 = sheet1.1 + sheet1.2 ; sheet2 = sheet1.2 + sheet 2.2 and sheet3 = sheet 1.3 + sheet2.3
Thank you in advance for any kind of advice.
Chris,
Excellent tutorial, thank you very much. Really helped me out in a business situation whereby we had 60 excels with different column names!
Matt
Hi Chris,
Excellent tutorial, thanks, really helped me out in a situation whereby we had to load 60 excel files with different column names!
Matt
Hi Chris,
Excellent article. Could you help me how to convert this to Power BI dataflow?
Thanks
Tibor
A simple way to solve this is just filter out the column headers
Hi,very interesting . I’m looking to combine this with combine few excel file. It’s possible to illustrate combine 2-3 excel files with 2 worksheets every time ? thanks
Hi Chris
Great article and it works great for my application with similar number of columns in all sheets.
But when there is a change in number of columns it did not show all columns.
For eg:
I added an extra column in one of the sheet and on load it did not show that column.
Is it possible to load all columns when number of columns in all sheet are not same? It can be adding a new column or deleting a column from any sheet.
Hello Chris – could I be as bold as to suggest that Excel.Document() does the trick – that gives you a list of Sheets, Tables and Defined Names in Excel. I add it as a custom Column and then filter this to get what I need. It avoids the need for Custom Functions.
For files of the same format (like budget files from multiple contributors), I stack them up, select the sheets/tables I am interested in, expand the content, promote the top row to be headings and then filter to remove the headings further down. I can prepare an example if that would help.
Apologies if this is covered elsewhere – I had a careful look for any mention and could not find a reference on this page.
Thanks for a great blog – I follow around half of it!!
Take care!
Hi Chris,
Some of the steps are confusing, is there a way you could upload a quick video of this above article. That may help many like me!
in the new may 2021 release you dont get edit just transform and relaod
Hi Chris, very useful article! thanks a lot
Hello I am trying to import data from excel workbook which has multiple sheets with different stuctures [different no. of columns] into Power BI.
Any suggestions will be appreciated !!
Thank you so much for this article, any idea how to do the same procedure but with different workbooks? Thanks in advance.
Thanks Chris, your blogs are always helpful and very detail. Appreciate taking time to share details.