OData feed expand does not display results

[GFinlayTerumo]

Hi

I have an OData feed coming from a Mendix application which I am consuming in PBI.  In order to try and cut down on the load / query times in the refresh I am trying to do my filtering on the OData feed directly.  I am however experiencing some issues with using expand.

I have a Table CaseManagement which is associated to the table Clinician, when I do

CaseManagement$select=ID,CaseIdentifier,CaseManagement_Clinician it correctly shows me the two column and the associated Record for Clinician. 

Instead of expanding the record I want to only see the Clinician.Name field in my table.  The issue that arises is that adding the expand query does not fail but it also does not return anything, even if I also add a select.

Does anyone have any experience in achieving what I am trying to do and can help me understand what I am doing wrong or if it is a limitation?

Thanks

Grant

[v-mdharahman]

Hi @GFinlayTerumo,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to optimise the time taking to query at refresh. As @rohit1991 and @lbendlin both already responded to your query, please go through the responses and mark the helpful reply as solution.

I would also take a moment to thank @rohit1991 and @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[rohit1991]

Hi @GFinlayTerumo ,

Hi Grant, you're on the right track with trying to optimize your OData query using $expand, but the issue you're running into—where Clinician.Name does not return anything despite using $expand=CaseManagement_Clinician($select=Name)—is likely due to either how the OData endpoint is structured in the Mendix application or a limitation in how $expand is implemented in that specific service.

Some OData implementations (especially custom ones like Mendix) may not fully support deep expansion or $select within expanded navigation properties unless they are explicitly configured to expose those nested fields.

To troubleshoot this, you can try a few things:

1. First, test the raw expanded URL in a browser or a REST client like Postman to ensure it returns data. If it returns nulls or empty objects, it means the service isn't exposing the expanded data properly.

2. If the expanded property appears as a Record in Power Query but doesn’t show Name even after using $select=Name, try removing the $select=Name and just use $expand=CaseManagement_Clinician—then manually expand the record in Power BI to see what fields are actually returned.

3. If nothing shows, it could be a data privacy issue or the navigation property isn’t configured to allow expansion at that depth in the service metadata.

As a fallback, you may need to load the Clinician table separately and perform the relationship in Power BI instead of trying to flatten it via $expand, especially if performance or compatibility is inconsistent. Let me know if you want help building a workaround query inside Power BI.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[GFinlayTerumo]

Hi @rohit1991 and @lbendlin thanks for taking the time to answer my query.

@rohit1991 

1) I have input my URL into Postman and it successfully returns what I am looking for;

2) Using expand=CaseManagement_Clinician does the same thing where it doesn't display anything.  Including it in my select returns the record and I can expand it manually to see Name, this is what I am trying to avoid though as am I right in saying this is generating a call to the server per line?

3) I don't think data privacy will be an issue as the account has access to all of the columns within all of the tables, would the naviagtion property you mentioned be on the Mendix side or PBI?  I'm unsure if this will be an issue as you can see Postman is returning correctly.

I've also loaded in the Clinicians table as it's own Query using OData however it doesn't seem to have changed anything.  I can successfuly manually expand the CaseManagement_Clinicians to get ID and create the relationship within the Model but I'd be as well manually expanding for Name.  Because the Mendix system is set up to display associations as a link as opposed to object id (see below) I do not have access to the IDs without returning the Record within the OData select.

@lbendlin 

The Mendix application is set up for OData 4 and the Clinician table has Name in it.

When manually expanding out the Clinician table and looking on Query Diagnoistics it looks like the following, I am unsure what to expect from this but it looks to be successfully doing what is being asked.

I also tried this with the expand in the URL and it returns a 200 code as well

Thanks

Grant

[lbendlin]

you should be able to run the same query as in the query diagnostics, and get the same result. If you don't get that then maybe you should raise a ticket.

[GFinlayTerumo]

Hi

Thanks the query diagnostics shows me that it loads successfully but doesn't give me any info about what it is outputting etc.  I will put in a ticket and if I get a resolution I will post a reply so as to close the post.

[lbendlin]

Make sure that your OData service supports V4, and that "Name"  is the actual field name. You could also manually expand that record and then use Query Diagnostics to see what the actual OData call looks like

In older versions you can do 

Example :

https://services.odata.org/V2/Northwind/Northwind.svc/Categories?$expand=Products&$select=Products/ProductName

[GFinlayTerumo · Accepted solution]

Hi @lbendlin @rohit1991 

I was able to resolve this issue by adding Table.ExpandRecordColumn to the start of my Source query and including the associated table within the OData Select statement.  Screenshot below with multiple nested Table.ExpandRecordColumn statements.

Thanks

Grant