Unable to Refresh Dynamicly Generated File Name Data Source

[Anonymous]

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?

[Anonymous · Accepted solution]

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]

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]

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]

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