Forum Discussion
Updating a Changing Data Source File Name
Hi all,
I have a client's web folder where daily .csv's are uploaded. The .csv files are identical in structure - all that changes is date at the beginning of the file name, which is always YYYYMMDD. They would like to have a dashboard that updates with the new daily file automatically, which I'm struggling to do. As an example, the predicament looks like this:
- On Day 1 I need to access clientwebsite.com/20180423_Data.csv
- On Day 2 I need to access clientwebsite.com/20180424_Data.csv
- On Day 3 I need to access clientwebsite.com/20180425_Data.csv
Any ideas / thoughts on how I might do this?
TIA,
SamB
Hi Anonymous,
I highligted the part of the code you were missing.
let Source = Csv.Document(Web.Contents( let today = DateTime.Date(DateTime.LocalNow()) in "https://https://www.clientsite.com/" & Number.ToText(Date.Year(today)) & Text.PadStart(Number.ToText(Date.Month(today)),2,"0") & Text.PadStart(Number.ToText(Date.Day(today)),2,"0") & "_users_per_country.csv"), [Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}) in #"Changed Type"The use of the code is to replace your URL with something dynamic while the remaining parts of your Source variable remain untouched.
25 Replies
You may not need this now. But just wanna add my two cents.
I used this one shot conversion
FileName = "(File Path)" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".csv"
Schedule refresh will not work as expected iin this situation as it keeps asking credentials for new file name.
Solution is given in the below link
Thanks Jay7 - sadly (or maybe thankfully!) the dashboard this was being used for is now defunct!
Cheers anyhow!
Sam
Hi Sam,
I've come across this problem before.
From the query editor, you can order the files from a folder into date order, so the most recent is on top, then only load the most recent file. Here's a great article explaining how to do it:
https://powerbi.tips/2016/06/loading-data-from-folder/
Thanks,
Martyn
Hi Martyn,
Thanks for your help - unfortunately the files are sitting on a website rather than a local folder and I don't seem to be able to access it via the 'Folder' method. To make things a bit more tricky there are other .csvs with similar names. So as well as clientwebsite.com/20180423_Data.csv there is also clientwebsite.com/20180423_DifferentData.csv also iterating by date everyday.
Thanks anyhow,
SamB
Will accesing the clients website via the main url or suburl provide a list of files similar toe the screenshot below?
To make things a bit more tricky there are other .csvs with similar names
How do you identify which csv should be selected as data source?
You can also make your url to change dynamically based on a date. Example
"https://www.clientsite.com/" & Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))) & Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0") & Text.PadStart(Number.ToText(Date.Day(DateTime.Date(DateTime.LocalNow()))),2,"0") & ".csv"
DateTime.LocalNow() is based on PC time if refreshed manually or server time if scheduled. Power BI service follows GMT +0.
