PBI app cannot load odata.feed with M functions in the URL

[Jonathan93]

Hello,

I have built an report where the source is odata.feed with an M function in the URI (to filter requierd data before downlaoding). For example a source request like

= OData.Feed("https://services.odata.org/northwind/northwind.svc/Customers?$filter=PostalCode ge '" 
& Number.ToText(12209) &"'", null, [Implementation="2.0"])

In PBI Desktop this works. When it is published to web , you cannot update the data under settings/datasets and the PBI web side displays the following error

Query contains unsupported function. Function name: OData.Feed

As far as I have tested, the problem occurs with all M functions. By replacing Number.ToText(12209) with "12209" the PBI app has no problem and the update the request accurately.

Is there a way around this problem, or am I doing something wrong?

Kind regards

Jonathan

[Jonathan93 · Accepted solution]

The problem ist solved if you follow the instructions of
https://blog.crossjoin.co.uk/2018/05/03/troubleshooting-data-refresh-performance-issues-with-odata-d...
and replace the previous oData with an oData without the Entity and choose the entity and the filters afterwards.

Source = OData.Feed("https://api.parliament.uk/odata"),
    GovernmentOrganisation_table =
    Source{[
        Name="GovernmentOrganisation",
        Signature="table"
    ]}[Data],
    #"Filtered Rows" =
    Table.SelectRows(
        GovernmentOrganisation_table,
        each ([GroupName] = "Cabinet Office")
    )

Then the M functions dont collide with PBI

[AlexanderK79]

Based on the previous posts and the work from: https://social.technet.microsoft.com/Forums/en-US/5d7ca0c6-36a1-4504-be74-5334fb498e8d/expressionerr... for the Web.Contents query instead of ODATA

This is a solution for retrieving data from Project Web App (Project Online) that works when refreshing in PowerBI Services:

It works with the Json.Document(Web.Contents( function instead of OData.Feed

// replace xxx with your tennant in the URL: https://xxx.sharepoint.com/sites/pwa/_api/ProjectServer/CustomFields
// replace Field_x0 and Field_x1 with the names of the fields in Project Online
// replace NewName_x0 and NewName_x1 with the names that you want to give to the new columns
// retrieve the custom fields (Custom_x0 and Custom_x1 in this code) that contain HTML in Project Online using:
// https://xxx.sharepoint.com/sites/pwa/_api/ProjectServer/CustomFields

let
PWA_url = "https://xxx.sharepoint.com/sites/pwa",
ProjectsRelativePath = if blReducedSet then "Projects()?$Filter=ProjectType ne 7 and substringof('Test', ProjectName) eq true" else "Projects()?$Filter=ProjectType ne 7",
Projects = Json.Document(Web.Contents(PWA_url&"/_api/ProjectData/[en-us]/",
[
Headers=[ #"Accept" ="application/json" ],
RelativePath = ProjectsRelativePath
])),
ProjectsList = Projects[value],
#"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectId", "ProjectName", "Field_x0", "Field_x1"}, {"ProjectId", "ProjectName", "Field_x0", "Field_x1"}),
#"Added HTML source" = Table.AddColumn(#"Expanded Column1", "HTMLsrc", each
if [Field_x0] is null and [Field_x1] is null then
Record.FromList({null}, {"d"})
else
Json.Document(Web.Contents(PWA_url&"/_api/ProjectServer/",
[
Headers=[ #"Accept" ="application/json;odata=verbose" ],
RelativePath = "Projects('"&[ProjectId]&"')/draft/IncludeCustomFields?$Select=Id,Name,Custom_x0,Custom_x1"
]))
),
#"Expanded HTMLsrc" = Table.ExpandRecordColumn(#"Added HTML source", "HTMLsrc", {"d"}, {"d"}),
#"Expanded d" = Table.ExpandRecordColumn(#"Expanded HTMLsrc", "d", {"Custom_x0", "Custom_x1"}, {"Custom_x0", "Custom_x1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded d",{{"Custom_x0", "NewName_x0"}, {"Custom_x1", "NewName_x1"}})
in
#"Renamed Columns"

// based on https://social.technet.microsoft.com/Forums/en-US/5d7ca0c6-36a1-4504-be74-5334fb498e8d/expressionerr... for the Web.Contents query instead of ODATA
// based on https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... for solving the dataset refresh error
// also inspired by https://community.powerbi.com/t5/Issues/HTML-formatting-for-Project-Online-Power-BI-Reports/idi-p/34...