Forum Discussion
Help on Combining Data from Multiple Worksheets into a Single Worksheet in Excel
Im so sorry for any confusion. I just really want to see the easiest way I can get this done. So let me just say what I do each time. Every month I have this data that I download from our help desk. I then want to try and make a better a visualization out of the data by creating a dashboard which I have shared with you in the previous posts. There are certain things I am trying to show as you probably saw. The reason why I created a column to calculate was because we want to see on average how many days it takes to complete a ticket, which is why I have those last 3 columns.
1. I see what you are saying here but I want to calculate the average days it takes to complete a ticket.
2. So you are just saying to copy and paste each time? I tried doing that in the past but it seems like too much to do each time. I have an original spreadsheet that contains data from August2018-July2020. So I created a table for each of those month and ran a Power Query to combine all those tables into one. Now my issue here is when I add another month to it, it sometimes messes up. When the combined data loads it will give me extra columns or some of the information is blank and im having to go back and enter it all in.
3. So I usually well add a new tab in my workbook and then go to my combined data tab to edit the query. When I do this, I combine the newest data but then like I said before it adds an extra column or will add blank information which I don't understand because the raw data has everything filled out already.
4. The screenshots I added in the previous post will show what I am talking about.
Jumping to the end of your response:
The steps you mentioned are correct. I turn that data into a table using the "Insert" toolbar.
What's the "Query" that you run. Is that the Power Query you referred to in your first post, the one that "adds additional information that isn't even in your worksheets?"
- Once I have all my months added in separate tabs(worksheets) I go to data and where it says "New Query" I go to from other sources and select blank query. From there I put in my formula =Excel.CurrentWorkbook () and enter. When I do that it pulls every table in the work book. But when I do that, it sometime will load blank information. I know this may all seem confusing and I may be making it more difficult then it should be but I just don't know how else to do it.
I'm going to step aside for the moment, since you do have Power Query at your disposal (I don't in my Mac), and let @Sergei Baklan help you with that process. It would seem to me that it should work.
My own experience, without it, is to that do that monthly copy and paste of the new month's data onto the bottom of a cumulative database is to do--in just one step--what you're trying to do in many. I do this with my monthly statements from credit cards and banks, into a single database to track our income and expenses....I let Excel (Pivot Table) do all the calculating. It is work, yes, but I would point out that you've been doing work and aren't satisfied with the results.
So I trust that Power Query will do the trick for you, the trick of combining all those monthly reports neatly and accurately....
Best wishes.
mathetes thank you for your help! I will try to do what you were mentioning as well and see what works better for me. I just wanted to find the easiest way to do this and be able to present it in a dashboard for a better visualization.
SergeiBaklan could you walk me through so that I don't mess up what you were trying to explain to me?
Both of your responses and ideas are great, I just need to try it out and see what works better for me. I do appreciate both of your assistance with this because its been giving me a headache trying to figure it out. I wouldn't say I'm in expert in excel but I just like to always learn more and find better ways to extract big data and provide a better visualization.
To test please put 2-3 of your files in some folder, let say c:\test. Use this connector
Select the folder on next step
Next click Transform Data
Power Query editor will be opened. Click on this button
Here select sample file or keep first one by default, and select the sheet / table with your data - they have to have the same name for all files
Result collects data from all your files in one table. You may do other transformations if necessary. Click here on File->Close and Load to. In your case perhaps it's better to select loading to data model
After that you may insert PivotTable with data model as the source. For the testing you may load result into Excel Table as well.
Okay, I have tried to follow your steps but it did not work so im not sure if I am doing something wrong. I have attached my screenshots for you to look at. Can you aslo let me know if my ribbons has everything I should have?
Thank you!
