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

Re: Updating a Changing Data Source File Name

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

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

Your csv.document is incomplete... Delimiter=";", Columns=2, Encoding=1252..





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

Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

Hi @danextian,

 

That's working perfectly now. Thank you so much for your help - it is much appreciated!

 

Cheers,

 

Sam

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

You're welcome. 






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

Re: Updating a Changing Data Source File Name

You may not need this now. But just wanna add my two cents.

I used this one shot conversion

 

FileName = "(File Path)" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".csv"

 

Schedule refresh will not work as expected iin this situation as it keeps asking credentials for new file name.

 

Solution is given in the below link

 

https://community.powerbi.com/t5/Integrations-with-Files-and/Unable-to-Refresh-Dynamicly-Generated-F...

 

Highlighted
Helper III
Helper III

Re: Updating a Changing Data Source File Name

Thanks @Jay7 - sadly (or maybe thankfully!) the dashboard this was being used for is now defunct! 

 

Cheers anyhow!

 

Sam

Highlighted
Frequent Visitor

Re: Updating a Changing Data Source File Name

Hi @danextian,

 

supposed there is no report for the day, will your code pick up the latest source file? Would it possible to use advanced editor to look for a specific string that is constant in the file name, say for example *users_per_country.csv?

 

Thanks!

Highlighted
Super User I
Super User I

Re: Updating a Changing Data Source File Name

Hi @djraguilar ,

 

supposed there is no report for the day, will your code pick up the latest source file?
What is your criterion for the latest data source? (filename, date modified, date created?) 


Would it possible to use advanced editor to look for a specific string that is constant in the file name, say for example 
*users_per_country.csv?

It is possible. You can filter the table to rows that ends with a specific text string. 

 Table.SelectRows(PreviousStep, each Text.End([Column], "users_per_country.csv"))

M is case sensitive so if your file name is not always in the same case, you can try this:

 Table.SelectRows(PreviousStep, each Text.End(Text.Lower([Column]), "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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

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.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors