cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Unable to Refresh Dynamicly Generated File Name Data Source

I am trying to use a file from SharePoint as a source for a dashboard. An updated version of this file is uploaded to this SharePoint folder every day with an incremented interger suffix at the end (file_name_253, file_name_254, etc).

 

To ensure I am getting the newest file I calulated the suffix number and applied it to the base file name. The only way I could figure out how to do this was using M. Below is a sample of the few lines that get this done.

 

FileSuffix = Int32.From(230 + Duration.Days(DateTime.Date(DateTime.LocalNow()) - Date.FromText("6/4/2018"))),
File = Text.Combine({"sharepointlocation/filename_", Text.From(FileSuffix), ".xlsx"}, ""),
Source = Excel.Workbook(Web.Contents(File), null, true),

This code seems to work and when I refresh on the desktop application my report successfully updates with the data from the newest file. However the issue arose when I deployed it to the Power BI Report Server and tried to schedule the refresh. On the Report Server, no data sources are recognized. The Data Source link is greyed out when you go to manage the report.

 

Same for Desktop Application when you go to the Data Source Settings. It returns with the following message: "We didn't find any data sources in this file."

 

Is there an issue with my code or a better way to retrieve the most recent file in the directory? Has anyone else experienced a similar issue?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Figured out a solution. 

 

I created a new query that just returned the newest file's name:

let
    FileSuffix = Int32.From(230 + Duration.Days(DateTime.LocalNow()) - Date.FromText("6/4/2018")),
    FileName = Text.Combine({"filename_", Text.From(FileSuffix), ".xlsx"}, "")
in 
    FileName

Then I used RelativePath to allow Power BI to validate against the root SharePoint folder instead of the full file path and then added the dynamically created filename (created above) as the relative path:

let 
Source = Excel.Workbook(Web.Contents("http://sharepointsite.com/site/folder/",
[
RelativePath = #'Newest File Name'
]), null, true),
...

Now the data source will show up in the list and allow me to enter credentials. Scheduled refresh on the Power BI Report Server succeeded as well.

 

I go the idea from the following blog posts:

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Figured out a solution. 

 

I created a new query that just returned the newest file's name:

let
    FileSuffix = Int32.From(230 + Duration.Days(DateTime.LocalNow()) - Date.FromText("6/4/2018")),
    FileName = Text.Combine({"filename_", Text.From(FileSuffix), ".xlsx"}, "")
in 
    FileName

Then I used RelativePath to allow Power BI to validate against the root SharePoint folder instead of the full file path and then added the dynamically created filename (created above) as the relative path:

let 
Source = Excel.Workbook(Web.Contents("http://sharepointsite.com/site/folder/",
[
RelativePath = #'Newest File Name'
]), null, true),
...

Now the data source will show up in the list and allow me to enter credentials. Scheduled refresh on the Power BI Report Server succeeded as well.

 

I go the idea from the following blog posts:

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

View solution in original post

Ross73312
Community Champion
Community Champion

Yeah it won't show anything because it can't tell you what the data source is.  It knows the data source at the point in time that the query is run, but because its dynamic you don't have a set data source. Your data source changes with your formula so the screens you are viewing can't tell you abstractly what it is.

(I no longer have access to this account)
Anonymous
Not applicable

That's what I was afraid of. Would it work if I generated the file name as a standalone query and pass the result in as a parameter to the SharePoint source? Or am I just out of luck with ever hoping to refresh a file with a changing name?

Ross73312
Community Champion
Community Champion

I'd be hesitant to state its impossible, but it is certainly something to be avoided.

(I no longer have access to this account)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors