Power BI Dynamic Data Refresh for Dynamic SharePoint URLs

[Anonymous]

I have an excel file with multiple sharepoints paths captured for different excel files. I want to create a power bi report which looks into this excel file and fetch listed excel files via the sharepoint path provided in the excel table. On power bi desktop everything is successful whereas after publishing, it's not refreshing. Can someone please help what could be the fix.

This is my first query:

Excel.Workbook(Web.Contents("https://xxxxx.yyyy.com/sites/ABCDEFGH/Apple%20Repository/0.%20Test/Small%20Improvement/PBI%20Develop..."), null, true){[Item="Table_APP_Projects_Master"]}[Data]

[Anonymous · Accepted solution]

I have managed to use a Power Automate flow to fetch files from the URL provided on excel and copy it over to a static location. From there Power BI would pick up and process. So, irrespective of dynamic entry in the source excel, Power BI would be able to fetch it

[MasonMA]

Hi, 

I'd suggest rebuilding the query using the SharePoint connector, not Web.Contents. Power BI built-in connectors are more reliable. 

For example:

let
    Source = SharePoint.Files("https://xxxxx.yyyy.com/sites/ABCDEFGH", [ApiVersion = 15]),
    Filtered = Table.SelectRows(Source, each Text.Contains([Folder Path], "Apple Repository/0. Test/Small Improvement/PBI Develop")),
    Imported = Table.PromoteHeaders(Excel.Workbook([Content], null, true){[Item="Table_APP_Projects_Master"]}[Data])
in
    Imported

This way in Power BI Service it will refresh properly since the connector supports OAuth authentication with SharePoint Online.

[Anonymous]

The paths are dynamic and is sourced from excel cells. FYR:

[v-hjannapu]

Hi @Anonymous,

your SharePoint paths are coming from Excel cells, Power BI Service is not able to refresh it. In Desktop it works because it uses your local connection, but in the Service the URL must be fixed and known while publishing.

You can try using the SharePoint Folder connector and then filter the required files in Power Query. This way, it keeps one common connection, and the refresh will work fine in the service.If your paths keep changing, then you may need to handle that part outside Power BI, like by using Power Automate or a Dataflow to bring all files into one location before loading them in Power BI.

please go through with the below document hope it may resolve your Issue
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Community Support Team.

[v-hjannapu]

Hi @Anonymous,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support team.

[Anonymous]

@v-hjannapu : I see you have provided a similar solution in the past. Can you please assist?

[v-hjannapu]

Hi @Anonymous,
Thank you for posting your query in Microsoft Fabric Community Forum. Also Thank you @MasonMA,
@GeraldGEmerick , for those inputs on this thread. 

your SharePoint paths are coming from Excel cells, that’s why the refresh is failing after publishing.

In Power BI Desktop it works fine because it uses your local access, but in Power BI Service, the URLs need to be fixed and known while creating the report. Dynamic paths coming from Excel can’t be read by the service during refresh.

You can try using the SharePoint Folder connector and then filter the files you need inside Power Query. This keeps one fixed connection and usually works fine after publishing.

please go through with the below document hope it may resolve your Issue

 https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Community Support Team.

[v-hjannapu]

Hi @Anonymous,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[Anonymous]

@GeraldGEmerick : Would we be able to parameterize base URL or do any such workarounds?

[GeraldGEmerick]

@Anonymous Power BI Service requires the URL to be fully evaluable at design time for scheduled refresh to work.