dynamic data source - schedule refresh issue for odata feed

[pallavi_r]

Hi All,

Can anyone please help me in resolving this issue I am facing with Odata call to sharepoint list metadata in Power BI Service.  This is working successfully and fast in desktop, but in service it gives the below error.

Step 1 -

Source = Odata.Feed(" https://sharepoint.com/sites/department1/_api/web/lists?")

Step 2 - Remove other columns

Step 3- Filter few rows

Step 4- Loop in through each list library in the department1 sharepoint site as below. Works perfectly fine in desktop but refresh issue in service

FieldValuesAsText=Table.AddColumn(#"FiletredRows", FieldValuesAsText, each OData.Feed("https://sharepoint.com/sites/department1/_api/web/lists/GetByTitle('"&[Title]&"')/Items?$select=FieldValuesAsText"))

Please help if anyone has resolved this kind of issues.

Thanks,

Pallavi

[pallavi_r · Accepted solution]

Hi All,

I have changed my approach, instead of using dynamic parameter for title, I am able to filter selected title and expand FieldValueAsText and select required columns.

Step 1 -

Source = Odata.Feed(" https://sharepoint.com/sites/department1/_api/web/lists?")

Step 2 - Remove other columns

Step 3- Filter selected libraries

Step 4 - Expand Items - FieldValuesAsText

Step 5- Selected required columns

Looping is not required with each title as parameter.

This approach or workaround works for me and I am able to successfully reduce the schedule refresh time significantly with odata compared to other sharepoint connectors.

Thanks,

Pallavi

[V-yubandi-msft]

Hi @pallavi_r ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

Thank You.

[V-yubandi-msft]

Hi @pallavi_r ,

Thanks for reaching out to us. In most cases, Power BI semantic models that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

First, check whether your dynamic data source can be refreshed, open the Data Source Settings dialog in Power Query Editor, and then select Data Sources In Current File. In the window that appears, look for the following warning message, as shown in the following image.

If that warning is present in the Data Source Settings dialog that appears, then a dynamic data source that cannot be refreshed in the Power BI service is present.

if your dynamic data source can be refreshed, then use the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

FYI: Data refresh in Power BI - Power BI | Microsoft Learn

For more detailed information, please review the document above as it will be more helpful for you. If you have any questions, let me know.

If my response solved your query, please mark it as the Accepted solution to help others find it easily!

And if my answer was helpful, I'd really appreciate a 'Kudos'.