Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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}
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?
This is what worked for me. Wish I saw this sooner!
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
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?
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
Thnks so much!
Success!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.