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

    • danextian's avatar
      danextian
      Super User
      8 years ago

      You can try this as your source url. The code is a bit long but you can assign DateTime.LocalNow() to a variable to shorten it.

       

      let today = DateTime.Date(DateTime.LocalNow())

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

       

      = "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") & 
"_users_per_country.csv"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi again,

         

        Thanks for your help on this. Would I copy and paste this into the Advanced Editor, or somewhere else? 

         

        Also, is there a reason you've split the code into two blocks?

         

        Many thanks,

         

        SamB