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 YYYYM...
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.
Here's the updated code and the strings that were changed.
let
Source = Date.From(DateTimeZone.LocalNow()),
DayOfWeek = Date.DayOfWeek(Source,1), //when 0, first day of the week starts Monday
LatestMondayDate = Date.AddDays(Source,-DayOfWeek ),
NameOfDay = Date.DayOfWeekName(Source ,"en-us"),
LatestDate = if NameOfDay = "Monday" then Source else LatestMondayDate ,
MMDDYYYY=
let
dt = LatestDate,
day = Text.PadStart( Text.From(Date.Day( dt)), 2,"0"),
mo = Text.PadStart( Text.From(Date.Month( dt)), 2,"0"),
yr = Text.From(Date.Year( dt))
in
mo & day & yr
in
"File_" & MMDDYYYY & ".xlsx"