All of the data modeling blogs that I have looked at seem to have the "data-data" (i.e., the rows/columns of financial data, or whatever, that is going to be analyzed) residing on one worksheet, and then other worksheets for a list of agents, list of products, list of whatever, which is all used as the source for pivot tables & charts. I have "data-data" (sorry, I don't know what else to call it) that is spread across 3 Excel worksheets (data from each of 3 help desk ticketing systems) and cannot be easily combined into one worksheet. The column headers in each of the 3 are the same, and the data in each column of the 3 is the same data type (Column C in the 3 worksheets contains open date, Column D contains the agent number, etc). I also have a separate worksheet for a list of agents, one for a list of products, list of countries & regions, etc., which I have previously used in VLOOKUPs for various forumulas in the past.
When it comes to the Data Model, I understand how the worksheets which contain the agent list, product list, etc., need to be added, but I do not understand how to get my 3 other worksheets (containing the data that is to be analyzed), noted properly in the Data Model so that I can do a total count, by agent, of how many tickets each person handled in each of the 3 ticketing systems, along with subtotals by country, grand totals, etc. I'm puzzled by how to get those 3 data worksheets to look as if it were 1 worksheet. I'm sure I'm just missing a basic point, but I'm not sure what it is. Thank you!
I think Mynda will come up with her statement that this is something typical for Power Query 😉
Not sure, but when you have your three worksheets. Do they have a certain limit in rows?
Let's say the first one 100 rows, the second one 1.000 and the third one 2.000 or whatever.
Is it possible to 'connect' them to your one datatable you want?
You then have them all in one worksheet and can do anything you like with it.
Maybe make a model first and copy and paste as values after the 'connection' and then save with a different name to work with it and start another time again with the model.
Hi Linda,
You're right, you need to consolidate the 3 sheets containing help desk ticketing data into one table, which you can then load to your data model. You can't do any modelling or pivoting of the data while it is in separate sheets/tables.
As Frans said, this is easily done with Power Query as described here:
https://www.myonlinetraininghub.com/combine-excel-worksheets-with-power-query
If you'd like to share a sample file with me, anonymize it if required, then I can insert the query so you can see it in action.
Mynda
