Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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.

 

 






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

23 REPLIES 23
Jay7
Frequent Visitor

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

 

Anonymous
Not applicable

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

 

Cheers anyhow!

 

Sam

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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

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

  






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

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

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.

 

 






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi @danextian, do you know of a way to do this when the file is updated weekly? So, for example, the online excel workbook is saved as "File_02062023.xlsx " at the start of one week, and updated as "File_02132023.xlsx " with the format of MMDDYYY? I am trying to find a way to automatically refresh this file in my report weekly without manually changing the suffix of the file.

 

Alternatively, since this is the only file in the folder and is updated weekly, is there a line of code that would allow me to select this .xlsx file without specifying the name of the file? Any insights would be greatly appreciated!

Hi @jf90 ,

Is the file being renamed or a new file is dumped in a folder? If it is the former, you can write a query to dynamically generate a filename based on today's date.

Assuming that the file is being updated every Sunday, the query below will generate the expected filename referencing a Sunday regardless of today's date.

let
    Source = Date.From(DateTimeZone.LocalNow()),
    DayOfWeek = Date.DayOfWeek(Source,0), //when 0, first day of the week starts Monday
    LatestSundayDate =  Date.AddDays(Source,-DayOfWeek ),
    NameOfDay =  Date.DayOfWeekName(LatestSundayDate,"en-us"),
    LatestDate = if NameOfDay = "Sunday" then Source else LatestSundayDate,
    MMDDYYYY= 
            let 
                dt = LatestDate,
                day = Text.PadStart( Text.From(Date.Day( dt)), 2,"0"),
                mo = Text.PadStart( Text.From(Date.Month( dt)), 2,"0"),
                yr = Text.From(Date.Year( dt))
            in 
                mo & day & yr
    
in 
    "File_" & MMDDYYYY & ".xlsx"

 

Say the name of this query is LatestFile, you can point your source to something like "folder/subfolder/subfolder2/" & LatestFile






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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Here's the updated code and the strings that were changed.

danextian_0-1676632443979.png

let
    Source = Date.From(DateTimeZone.LocalNow()),
    DayOfWeek = Date.DayOfWeek(Source,1), //when 0, first day of the week starts Monday
    LatestMondayDate =  Date.AddDays(Source,-DayOfWeek ),
    NameOfDay =  Date.DayOfWeekName(Source ,"en-us"),
    LatestDate = if NameOfDay = "Monday" then Source else LatestMondayDate ,
    MMDDYYYY= 
            let 
                dt = LatestDate,
                day = Text.PadStart( Text.From(Date.Day( dt)), 2,"0"),
                mo = Text.PadStart( Text.From(Date.Month( dt)), 2,"0"),
                yr = Text.From(Date.Year( dt))
            in 
                mo & day & yr
    
in 
   "File_" & MMDDYYYY & ".xlsx"





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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Amazing--thank you so much @danextian!

Amazing--thank you @danextian! The only change would be that the file updates every monday, instead of Sunday. How would you go about making that tweak to the code? Much appreciated!

Anonymous
Not applicable

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!

Hi @Anonymous ,

 

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."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors