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

danextian's avatar
danextian
Super User
8 years ago

Hi Anonymous

 

Both M scripts are independent from one another but lead to the same result. If you placed them in the PQ formula bar, both would return the URL+ YYYYMMD format of todays date + your additional text strings.  In the first one, I simply assigned  DateTime.Date( DateTime.LocalNow() ) to a variable named today.  The second one is a longer and can be confusing way of writing a formula wherein,  instead of assigning DateTime.Date( DateTime.LocalNow() ) to a variable, I repeated it multiple times in the code. The first one though  could still be written in a more elegant way.

 

Now where to place either?

 

Normally, a query start with Source variable. Click on Source in the Query Settings pane and in the formula bar, replace the hardcoded url with the code I gave you.  Web.Contents(Csv.Document(

= Web.Contents(Csv.Document( 
    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", the remaining part of your code...

 

or

 

= Web.Contents(Csv.Document( 
     "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", the remaining part of your code...