Forum Discussion

Jigyasa's avatar
Jigyasa
Copper Contributor
Dec 21, 2022

Best way to combine 400 small tables in Excel?

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 17...
BI & Data Analysis
excel
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 21, 2022

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.

 

 

Jigyasa's avatar
Jigyasa
Copper Contributor
Dec 21, 2022
I have 27 sheets in the same workbook and I'm not quite sure what you mean by the same name as Excel doesn't allow me to have two tables with the same name. As for columns, they are mostly similar but have one difference of each client's names which I need to extract.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Dec 21, 2022

    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.

     

     

     

     

    • Jigyasa's avatar
      Jigyasa
      Copper Contributor
      Dec 21, 2022
      This is essentially what I've done just that I've gone the long route and added it table by table but I'm not sure if it can refresh any faster
    • Jigyasa's avatar
      Jigyasa
      Copper Contributor
      Dec 21, 2022
      Hi,
      The only issue with this is, my tables look like this
      https://ibb.co/MBB7NBX
      and each worksheet is monthly data. So when I upload them individually to a query, I'm extracting the first column name to create a client column and adding a new field to include which month the table is from. The month information is in the table name, example: clientABC_july22, but I have not been able to import.

      So essentially the reason I'm uploading each table individually is so I can include the client name and month of the table into the data so that I can differentiate this data when I eventually append it all into one big table.
      • Jigyasa's avatar
        Jigyasa
        Copper Contributor
        Dec 21, 2022
        https://www.linkpicture.com/q/tables_1.png

        Apologies for the bad link.

Resources