Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jonathan93
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Jonathan93
Frequent Visitor

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

View solution in original post

2 REPLIES 2
Jonathan93
Frequent Visitor

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors