Updating a Changing Data Source File Name

Hi Anonymous ,

supposed there is no report for the day, will your code pick up the latest source file?
What is your criterion for the latest data source? (filename, date modified, date created?) 

Would it possible to use advanced editor to look for a specific string that is constant in the file name, say for example *users_per_country.csv?

It is possible. You can filter the table to rows that ends with a specific text string. 

 Table.SelectRows(PreviousStep, each Text.End([Column], "users_per_country.csv"))

M is case sensitive so if your file name is not always in the same case, you can try this:

 Table.SelectRows(PreviousStep, each Text.End(Text.Lower([Column]), "users_per_country.csv"))

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"

Great, thanks - I'll have a go with this :manvery-happy:

I  realized the code I posted have line breaks. It's good for readability but pasting them into the formula bar would cause an error. Instead go to advanced editor and replace the appropriate part of your source variable.

Hi again,

I'm get a Expression.SyntaxError: Token Comma expected. message when I try your solution.

So this is what's in the Advanced Editor once I pull in my data initially:

let
Source = Csv.Document(Web.Contents("https://clientsite.com/20180425_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"

and then this is what I replace it with:

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",    

   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Changed Type"

Any ideas? Your help is much appreciated BTW!

SamB

Hi danextian,

That's working perfectly now. Thank you so much for your help - it is much appreciated!

Cheers,

Sam

You're welcome. 

Hi danextian,

supposed there is no report for the day, will your code pick up the latest source file? Would it possible to use advanced editor to look for a specific string that is constant in the file name, say for example *users_per_country.csv?

Thanks!

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!

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!

Amazing--thank you so much danextian!

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.