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 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
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.
