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.
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...Great, thanks - I'll have a go with this :manvery-happy: