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 ,

The fix is to use Table.ExpandRecordColumn right after your Source step, and make sure your OData query includes both $expand and $select for the related table/fields you need. This makes those nested fields show up as regular columns that you can work with in Power BI. Example in Power Query (M):

let
    Source = OData.Feed("https://your-odata-url"),
    Expanded = Table.ExpandRecordColumn(Source, "Clinician", {"Name"}, {"Clinician.Name"})
in
    Expanded

Double-check that your OData service supports V4 and that your field names match exactly (case matters). If things still look weird, run your query in Query Diagnostics or even Postman to see what’s coming back. You might need to expand more than once if you have multiple nested levels.

[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