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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors