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

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.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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