Dec 21 2022 01:18 AM
I have about 27 worksheets with 15 smaller tables on each worksheet that I need to connect into one major table to eventually create a dashboard. I used Power Query to do this. So far I've reached 170 queries and it takes a very long time to refresh. I cannot change the main tables as it is easier for everyone else to view and they update the data in these tables throughout the month. I'm looking for an alternative, would it really be faster if I simply tried to make a master table individually referencing the value in each table. I thought power query method would be more efficient. Please let me know if there is an alternative I haven't thought of yet?
Dec 21 2022 01:54 AM
@Jigyasa Are you using the From Folder connector? If you have the 27 sheets in one Folder, and the 15 tables have the same names in each of them, then you would need 1 query for each group of tables. That is 15!
So, for example each query will connect to all tables called "Table1" in all 27 workbooks, creating one large Table1. Do that for all 15 tables. And then you can append the 15 large tables into one.
Dec 21 2022 02:00 AM
Dec 21 2022 02:15 AM
Hi, @Jigyasa
You may want to look into: Consolidate data in multiple worksheets https://support.microsoft.com/en-us/office/consolidate-data-in-multiple-worksheets-007ce8f4-2fae-4fe...
-Ramy
Dec 21 2022 02:26 AM
@JigyasaI thought you had 27 workbooks, each with 15 tables in it. So, you have one workbook, with 27 sheets in it, each having 15 tables on it?
And my I presume that the tables are structured Excel tables.
If so, then connect to the workbook. Select the workbook name in the Navigator and press transform.
In the next screen you can, for example, filter all rows where the Kind is Table and then expand the Data column.
If all of this does not make sense, explain how you are currently connecting to all the tables. Include screenshots like the ones above, showing the steps you take.
Dec 21 2022 03:48 AM
Dec 21 2022 03:51 AM
Dec 21 2022 03:56 AM