Automatically Appending Power Queries?

[Anonymous]

Hello! I was hoping to get some help with a worksheet I am working on.

Currently, I have about 25 different tables that I have combined into one large power query. Each of these tables is in the same file, just on different sheets (So I am only using 1 excel file). In the future, more tables will need to be added, so I was wondering if there was a way to automate the appending process so that when I create a new table, it will automatically be added to my master power query.

The sheet I am creating is going to be used by people who know nothing about power query, so I am trying to make it so that a new table can be added in as few clicks as possible. Thanks! 🙂

[Vijay_A_Verma · Accepted solution]

Yes, you will need to use = Excel.CurrentWorkbook()

This will ensure whenever you add a table in current workbook, they will be appended. But yes, you will need to refresh the query (either manually or through a macro)

I have demonstrated this in below Excel workbook where there are 3 tables and 4th range which I haven't converted into table.

When you convert 4th range into table and you refresh the query, the 4th table will also get appended.

Also I have applied filter to remove Result table otherwise Result table will also get appended.

https://1drv.ms/x/s!Akd5y6ruJhvhugPyKn-oq4n4Owva?e=LlP869 

[Anonymous]

This is exactly what im looking for! is there any way I could have yu explain the steps on how you did it?

[Vijay_A_Verma]

Steps are given in the query itself. In Excel - Data - Queries and Connections - Click on the Query to see query steps

[otravers]

Loading data from new sheets created manually by many users is a recipe for the whole thing to break sooner or later because of operator error. Consider creating a table object for data entry in Excel that will help enforce the right structure and ease ingestion on the Power Query side. You can always more tables in additional sheets if the first one becomes too big, but that should apply so rarely that it's not really worth automating.

https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e...