cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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 YYYYMMDD. They would like to have a dashboard that updates with the new daily file automatically, which I'm struggling to do. As an example, the predicament looks like this:

 

- On Day 1 I need to access clientwebsite.com/20180423_Data.csv

- On Day 2 I need to access clientwebsite.com/20180424_Data.csv

- On Day 3 I need to access clientwebsite.com/20180425_Data.csv

 

Any ideas / thoughts on how I might do this?

 

TIA,

 

SamB

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

Hi @SamBerger,

 

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.

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

18 REPLIES 18
Highlighted
Resolver I
Resolver I

Re: Updating a Changing Data Source File Name

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

 

Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

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

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

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

 

 

folder.png

 

 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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

Hi there,

 

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

 

Capture.PNG

 

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

 

Thanks!

 

Sam

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

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"

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

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

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

Hi @SamBerger

 

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

  






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

Great, thanks - I'll have a go with this Man Very Happy

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors