Odata data refresh issue with custom function

[SamWhitehouse]

Hi, 

I am currently experiencing issues with automatic data refreshing in my power bi report. 

I am first creating a function that looks at all sharepoint subsites

let loadIssues = (site as text) =>
let
Source = OData.Feed(site&"/_api/web/lists/GetByTitle('Issues')/Items()?$Select=Title")
in
Source
in loadIssues

I then have another table that has a Custom Function that looks at each 'site' and looks get the list of issues. 

This works fine, and pulls through the items I need. However, I am not able to refresh the data set in the Power Bi Service and cannot set an automatic data refresh. I have to manually refresh the data each time and re-publish the report into the Power Bi service. 

When trying to set auto refresh,the below item appears:

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Issues

Query contains unsupported function. Function name: OData.Feed

any ideas?

[dax]

Hi SamWhitehouse,

I find a similar post for this problem, you could try to refer to PBI app cannot load odata.feed with M functions in the URL  for details.

Best Regards,
Zoe Zhi

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

[SamWhitehouse]

Thanks for the response. So from what I read the issue is with Query Folding not taking place. However, the examples use SQL querys which i am not familliar with so I cant see what is my query I can change to get Query Folding to take place.

My Function 'Query 1' that i may need to modify is below:

let loadIssues = (site as text) =>
let
Source = OData.Feed(site&"/_api/web/lists/GetByTitle('Risks')/Items()?$Select=Title")
in
Source
in loadIssues

My Table that envokes the above function can be seem below (Again, not sure if something needs to change for folding to work.)

let
Source = OData.Feed("SharepointSite/_api/ProjectData/Projects()?$select=ProjectName,ProjectWorkspaceInternalUrl&$filter=ProjectName eq 'TestProject'", null, [Implementation="2.0"]),
#"Invoked Custom Function" = Table.AddColumn(Source, "Query1", each Query1([ProjectWorkspaceInternalUrl])),
#"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query1", {"Title"}, {"Query1.Title"})
in
#"Expanded Query1"