Forum Discussion
Updating a Changing Data Source File Name
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.
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.
Hi there,
Yes - it looks like this, although it's via a URL rather than the local path.
In terms of choosing, I would want YYYYMMDD_users_per_country,csv in this example.
Thanks!
Sam
You can try this as your source url. The code is a bit long but you can assign DateTime.LocalNow() to a variable to shorten it.
let today = DateTime.Date(DateTime.LocalNow()) in "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"= "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") & "_users_per_country.csv"
Hi again,
Thanks for your help on this. Would I copy and paste this into the Advanced Editor, or somewhere else?
Also, is there a reason you've split the code into two blocks?
Many thanks,
SamB
Hi Anonymous
Both M scripts are independent from one another but lead to the same result. If you placed them in the PQ formula bar, both would return the URL+ YYYYMMD format of todays date + your additional text strings. In the first one, I simply assigned DateTime.Date( DateTime.LocalNow() ) to a variable named today. The second one is a longer and can be confusing way of writing a formula wherein, instead of assigning DateTime.Date( DateTime.LocalNow() ) to a variable, I repeated it multiple times in the code. The first one though could still be written in a more elegant way.
Now where to place either?
Normally, a query start with Source variable. Click on Source in the Query Settings pane and in the formula bar, replace the hardcoded url with the code I gave you. Web.Contents(Csv.Document(
= Web.Contents(Csv.Document(
let today = DateTime.Date(DateTime.LocalNow()) in "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", the remaining part of your code...or
= Web.Contents(Csv.Document(
"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") & "_users_per_country.csv", the remaining part of your code...