When the file name changes depending on the date

[owlet0214]

Hello, from Japan

If the file name changes depending on the date, can I fix it by modifying the code?

For example, in the case of today, an expression describing yesterday's date is placed. Get with the following query.

Excel.Workbook(Web.Contents("https://www.aaa.com/files/documents/worldwide-2020-06-28.xlsx"), null, true)

On the next day, change the file name and get it as follows.

Excel.Workbook(Web.Contents("https://www.aaa.com/files/documents/worldwide-2020-06-29.xlsx"), null, true)

I want to read "worldwide-*.xlsx" without any changes in the query editor.

Please help

[Uno707]

Is there any thing you can do when the new date to the file is unknown? I.e. the file is named with the date it was updated however you don't know when the data source will be updated each time. 

[MattAllington]

What I normally do is this. 

select "load from folder" instead of load from file

don't combine the files, but click Transform

add a new column that extracts the date, convert it to a date

sort descending so it keeps the latest

load the top file (click binary, but then change the ref to be absolute ref to the first file). I normally do this by changing the code name="yada", Folder="something" and replace it with {0}

[Czempijan]

what if files are on Sharepoint? I use similiar techinque for folders on my drive and files on SP. however seems power query cannot connect to folder on SP? how to bypass this?

[Uno707]

This is what worked for me. Wish I saw this sooner! 

[Anonymous]

try this:

let
partDate=DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd"),

Origine = Excel.Workbook(Web.Contents("https://www.aaa.com/files/documents/worldwide-"& partDate & ".xlsx"), null, true)
in
Origine

[owlet0214]

Thanks!

But I get "worldwide-2020-06-29.xlsx ".

I want date of yesterday "worldwide-2020-06-28.xlsx"

Is it possible to get the date of the previous day?

[Anonymous · Accepted solution]

let

partDate=DateTime.ToText(Date.AddDays( DateTime.LocalNow(),-1), "yyyy-MM-dd"),

Origine = Excel.Workbook(Web.Contents("https://www.aaa.com/files/documents/worldwide-"& partDate & ".xlsx"), null, true)

in Origine

[owlet0214]

Thnks so much！
Success!