Forum Discussion
When the file name changes depending on the date
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
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?
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
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?
