Forum Discussion
When the file name changes depending on the date
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
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
8 Replies
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}
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
OrigineThanks!
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?
This is what worked for me. Wish I saw this sooner!
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?
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.
