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"Hello danextian,
What about importing data from excel, where its shared and being updated daily, and the title changes daily with references to the date. What should i do to prevent a Power BI error when the excel workbook title changes. An example of how the change will be like is "Dashboard (1 Mar 2024)" to "Dashboard (2 Mar 2024)".
HI Anonymous ,
Try this:
= let today = Date.From(DateTime.LocalNow()), formatted = Date.ToText(today, "(d MMM yyyy)" ) in "Dashboard " & formatted & ".xlsx"This works if it i just the date format that changes. Other than that, it is a different story.