Automate newly generated files in Power Query

[Paritae]

I have a folder which generates each day new files. There are multiple files being generated with different names. In the names is also the day included when it is being generated.

I need to import them and every new file being generated into power query.

The files which have the same name (expect the exportet date of course) need to be appended.

Before that the files need to take the date in the name and create a column "Date" in the regarded file.

I know how to process everything. Loading the data by adding the folder. But I don't get how I can automate everything so my functions applyed by the first file will be used for every file which has the same file name (expect of tha date in the name). If I can add a column with the date, I would be able to append afterwards and apply my function then. But that still leaves open how I can automate adding a new column with date with each new file.

I also tried to add the column in a file but it just does not work. I tried to work with something like this but there are always errors:
Date = Date.From(DateTime.FromText(Text.BetweenDelimiters([FileName], "yourStartDelimiter", "yourEndDelimiter")))

[Paritae]

So far I get the table form a M365 Sharepoint. But it could be that in the future it will be over a Delta Lake.

The table looks something like this:

ID	Adress	Value
123	Street 1	50
789	Street 2	60

And for each day I receive a new table in this form. The name is 2023-10-18_Data, 2023-10-19_Data, etc.

How can I tell Power Query that it needs to combine each new table into a new one? As far as I know I need to actively select table to be appended. But I need it to be automated for every table in a folder...

[BA_Pete]

Hi @Paritae ,

If your files are in a network folder, then you'll want to use the Folder Connector. You'll also need a gateway if you want to refresh in the Service:

https://learn.microsoft.com/en-us/power-query/connectors/folder 

If your files are in a SharePoint folder, you'll want the SharePoint Folder connector. No gateway required:

https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder 

These connectors both work essentially the same way. The part you're looking for is where you get the option to "Combine & Transform" or something similar to that wording. This will allow you to filter to an entire folder, transform all the files in the same way based on a sample file, and append them all at the end.

Regarding your new date column, I'd need to see an example of one of your file names to be able to give you the right code, but it should be easy enough to implement with the above connectors by adding it, then slightly adjusting which columns get removed by the auto-generated code.

Pete