Combine all tables in workbook and pick up any new ones added | Power Query

Combine all tables in workbook and pick up any new ones added

baxbax

Member

Members

Power Query

Power BI

Forum Posts: 19

Member Since:
July 11, 2016

Offline

September 27, 2017 - 10:26 pm

Hi,

I have a workbook that contains multiple sheets. All of the sheets have identical formats. Each sheet has a table that contains summary data. I want to combine these in to one table for all sheets. Currently I have done this by creating a connection only query for each table and then appending them to get my combined table.

From time to time new sheets will be added to the workbook and I will want to include these in to the combined sheet.

I know that if you create a query from a folder you can get it to automatically include any new files added to the folder in your query. Is there any similar functionality whereby I can automatically include any new worksheets and tables in to my combined query in the scenario above?

Thanks

Mark

See Answer

Mynda Treacy

Admin

Forum Posts: 732

Member Since:
July 16, 2010

Offline

September 28, 2017 - 11:06 am

Hi Mark,

You can use the Excel.CurrentWorkbook function to do this.

Create a blank query in your Excel file that contains the tables you want to combine. In the Query Editor formula bar type:

=Excel.CurrentWorkbook()

Press ENTER

This will bring up a list of all the tables, sheets and named ranges in your file. Filter the Name column to select the items you want.

Tip: name all of your tables with a common beginning or ending. e.g. I start the name of all my tables with 'tbl_...' so when I filter the list I can simply specify Text that Begins With; tbl_ and then when I add any new tables they will be included in the query.

Mynda