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.
Hi jf90 ,
Is the file being renamed or a new file is dumped in a folder? If it is the former, you can write a query to dynamically generate a filename based on today's date.
Assuming that the file is being updated every Sunday, the query below will generate the expected filename referencing a Sunday regardless of today's date.
let
Source = Date.From(DateTimeZone.LocalNow()),
DayOfWeek = Date.DayOfWeek(Source,0), //when 0, first day of the week starts Monday
LatestSundayDate = Date.AddDays(Source,-DayOfWeek ),
NameOfDay = Date.DayOfWeekName(LatestSundayDate,"en-us"),
LatestDate = if NameOfDay = "Sunday" then Source else LatestSundayDate,
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"
Say the name of this query is LatestFile, you can point your source to something like "folder/subfolder/subfolder2/" & LatestFile
