Dynamic Data Source - Excel Spreadsheet

[elaine1217]

Hello, I have had issues with dynamic data sources before, but the resolution I used last time isn't working for me this time around. 

I have a dashboard that uses a couple of Excel spreadsheets that are updated by many users; the location and file name changes fairly often, new versions are created, etc. This was becoming a real hassle as everytime a file name or location changed, the dashboard would fail refresh. To avoid this, I created a spreadsheet that users update that lists the filepath and file name for the most current version of each Excel. Then, in PowerBI I have queries that import each line of the Excel. 

This works fine in Desktop, but it is a dynamic data source and service won't allow refreshes, which is really necessary for this dashboard. I wanted to see if either a) anyone could help updating this query to make the source not dynamic or b) if anyone has a better solution they have found for when files change name/location often. 

Filtered Rows2 is the table listing each excel file's name, it's file path, file name, etc. For each query, i filter to a specific row then try to get the contents of the first row. The "Contents" step is what is causing the 'dynamic data source' error message. 

#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Resource] = "Filter Value")),
Contents = Web.Contents(#"Filtered Rows2"{0}[FullPath])

Thanks for any insight or assistance!

[lbendlin · Accepted solution]

 To avoid this, I created a spreadsheet that users update that lists the filepath and file name for the most current version of each Excel.

That invites user errors.  Instead, have them place the "latest"  files in a specific sharepoint folder. Then in your Power BI you connect to that folder (static connection) and grab whatever files are in the folder.  That way you can refresh in the service _and_ don't need a gateway.

[SaiTejaTalasila · Accepted solution]

Hi @elaine1217 ,

Incase if they are uploading all files to the same SharePoint then you can ask them to create the file name in specific format .It helps you to filter the files easily.example-<file name etc,..>_elaine.xlsx.

In transform data you can apply contains Elaine and filter file names and use split column name and you can further filter it based on requirement.

Thanks,

Sai Teja 

[v-tangjie-msft · Accepted solution]

Hi @elaine1217 ,

Thanks to @SaiTejaTalasila  and @lbendlin  for the quick reply and solution. Please allow me to offer other insights:

Please refer to the following documents to use RelativePath with Web.Contents():

Solved: Dynamic data sources aren't refreshed in the Power... - Microsoft Fabric Community

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query 

https://tengkuma.vercel.app/blog/posts/conquering-urls-with-relativepath-and-query-in-power-bi

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[SaiTejaTalasila · Accepted solution]

Hi @elaine1217 ,

Incase if they are uploading all files to the same SharePoint then you can ask them to create the file name in specific format .It helps you to filter the files easily.example-<file name etc,..>_elaine.xlsx.

In transform data you can apply contains Elaine and filter file names and use split column name and you can further filter it based on requirement.

Thanks,

Sai Teja 

[lbendlin · Accepted solution]

 To avoid this, I created a spreadsheet that users update that lists the filepath and file name for the most current version of each Excel.

That invites user errors.  Instead, have them place the "latest"  files in a specific sharepoint folder. Then in your Power BI you connect to that folder (static connection) and grab whatever files are in the folder.  That way you can refresh in the service _and_ don't need a gateway.