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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

Anonymous
Not applicable

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.

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?

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors