Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • danextian's avatar
    danextian
    8 years ago

    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.

     

     

danextian's avatar
danextian
Super User
8 years ago

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.

 

 

jf90's avatar
jf90
Frequent Visitor
3 years ago

Hi danextian, do you know of a way to do this when the file is updated weekly? So, for example, the online excel workbook is saved as "File_02062023.xlsx " at the start of one week, and updated as "File_02132023.xlsx " with the format of MMDDYYY? I am trying to find a way to automatically refresh this file in my report weekly without manually changing the suffix of the file.

 

Alternatively, since this is the only file in the folder and is updated weekly, is there a line of code that would allow me to select this .xlsx file without specifying the name of the file? Any insights would be greatly appreciated!

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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"
  • jf90's avatar
    jf90
    Frequent Visitor
    3 years ago

    Amazing--thank you danextian! The only change would be that the file updates every monday, instead of Sunday. How would you go about making that tweak to the code? Much appreciated!

  • jf90's avatar
    jf90
    Frequent Visitor
    3 years ago

    Amazing--thank you so much danextian!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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.