How to automatically refresh when file name is slightly different each time (timestamp at the end)

[jasmine9]

HelloI have a requirement to load csv files from an internal tool into PowerBI. The challenge is that the generated filenames have a date/timestamp as the suffix. For eg, todays Employee file will be named as Employee_8_18_2020_21_30_09.csv

I thought I can change the datasource to have 3 filepath parts. 

1- Path+Employee

2-*

3-.csv

But I am getting the error "DataFormat.Error: Illegal characters in path."

Can you guide me on how to refresh data when the file name ends with different characters each time?

Thank you in advance,

Jas.

[fhill · Accepted solution]

Import a FOLDER and 'Combine & Edit' to get to Power Query.  On the 'Source' Step you should have a list of files in the Folder.  While 'Source' is selected in Applied Steps, Add a Filter to the 'Name' column:

We are going to 'Filter' the File Names while on the 'Source' Step, to add a NEW Step removing the files we don't want...

Now a new 'Filtered Rows' step has appeared, and all following steps will not see the bad files.

[jasmine9]

Thank you for each one of you for contributing to this post! I got my data loads to work without any issues using the Import folder option and then using PowerQuery to filter the filename. 

Appreciate all your time on this!!

-Jas.

[amitchandak]

@jasmine9 , refer if this can help

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4...

[jasmine9]

Thank you for the suggestion. But the referred article implies that the import file is in its own unique folder. 

In my case, I have about 13 different files to import in my "Downloads" folder, so it would be manual work each day to move them into individual folders. 

Any way to change the data source to point to "starts with" filename instead of the complete filename?

Thanks,

Jas

[jthomson]

If you import the folder as a whole, you should have a step where it'll list each file name, which you can then filter down like you want in Power Query

[jasmine9]

When I import the folder as a whole, PowerBI wants to combine all the files in the folder into one table. I have spent hours to try and see if I can change it, but it always seems to want to combine the files, even though the files have very different format.. 

[jasmine9]

@jthomson   I see what you mean. I can use powerquery to define filename "starts with". Let me run with this. I will provide an update, thank you!!