How to append Excel sheets with wildcard

[bas565]

Hi,

I'm used to Tableau where i can use a wildcard union option to combine data. (In this case all sheets that start "DATA*" in the file). The Append function in Power BI seems like i have to manually select the sheets. I think i can probably use M Code to make it dynamic but am not sure how. (Each month the User adds a new tab of data E.g. DATA Mar 23). 
= Table.Combine({#"Changed Type", #"Data"*})  editing the M-Code to something like this doesn't work. 

I have used a web link to connect to an Excel file on Sharepoint. 

There's also an option in Tableau, that i can't work out in Power BI to add the Path as a column. Eg. to differentiate between each appended table, so would show the Month Year. In the example above would get DATA Mar 23 as the column contents for that part of the table.

Many Thanks. 

[bas565]

That worked. Thanks!

[BA_Pete · Accepted solution]

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