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.

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Sam,

 

I've come across this problem before.

 

From the query editor, you can order the files from a folder into date order, so the most recent is on top, then only load the most recent file.  Here's a great article explaining how to do it:

 

https://powerbi.tips/2016/06/loading-data-from-folder/

 

Thanks,

 

Martyn

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Martyn,

     

    Thanks for your help - unfortunately the files are sitting on a website rather than a local folder and I don't seem to be able to access it via the 'Folder' method. To make things a bit more tricky there are other .csvs with similar names. So as well as clientwebsite.com/20180423_Data.csv there is also clientwebsite.com/20180423_DifferentData.csv also iterating by date everyday.

     

    Thanks anyhow,

     

    SamB

    • danextian's avatar
      danextian
      Super User
      8 years ago

      Will accesing the clients website via the main url or suburl provide a list of files similar toe the screenshot below? 

       

       

       

       To make things a bit more tricky there are other .csvs with similar names

      How do you identify which csv should be selected as data source?

       

      You can also make your url to change dynamically based on a date. Example

       "https://www.clientsite.com/" &  
Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow()))) &
Text.PadStart(Number.ToText(Date.Month(DateTime.Date(DateTime.LocalNow()))),2,"0") & 
Text.PadStart(Number.ToText(Date.Day(DateTime.Date(DateTime.LocalNow()))),2,"0") & ".csv"

      DateTime.LocalNow() is based on PC time if refreshed manually or server time if scheduled. Power BI service follows GMT +0.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi there,

         

        Yes - it looks like this, although it's via a URL rather than the local path. 

         

         

        In terms of choosing, I would want YYYYMMDD_users_per_country,csv in this example.

         

        Thanks!

         

        Sam