How to append Excel sheets with wildcard

Hi bas565 ,

*EDIT* The method is slightly different for SharePoint, but the priciple is the same. See end for details.

Connect to your workbook in Power Query and select just one tab to import. Once imported, you will see a few query steps that have been auto-generated, like this:

Delete all the steps except the Source step. This will get you back to a table representation of the workbook, with each tab being its own row.

You can filter the [Name] column using Text Filters > Begins With "DATA":

This should filter your workbook table to just these tabs.

Then you can go ahead and click to expand the [Data] column to automatically append all these tabs:

*EDIT* SharePoint method:

Use this connection string as your Source step (with your own details, obvs):

This should give you the full file list of your SP files. Again, you can filter on [Folder Path] and [Name] to get the file you need.

Once you've identified the file you want, then click on the green 'Binary' word to get to your tab list table.

Pete