Power BI Dynamic Data Refresh for Dynamic SharePoint URLs

[haleem3388]

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]

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

[haleem3388]

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

[v-hjannapu]

Hi @haleem3388,

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 @haleem3388,
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.

[haleem3388]

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

[v-hjannapu]

Hi @haleem3388,
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 @haleem3388,
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.

[haleem3388]

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

[GeraldGEmerick]

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