Data not loaded correctly in Excel and Power BI Desktop with the use of $expand in OData feed

[peterkovecses]

Hello, I am using .NET 8 with Microsoft.AspNetCore.OData 8.2.5 package.
When I use an OData feed URL containing an $expand query in Excel or Power BI Desktop applications, only the expanded columns appear in the results.
For example, when using a URL like https://localhost:7289/odata/Books?$expand=authors only the author-related columns are displayed, while other columns from the Books entity are not loaded correctly.

I already started an OData issue in this regard, but it was closed with the fact that this issue is not caused by my code or the OData package, it is probably an Excel/Power BI Desktop issue.

Data not loaded correctly in Excel and Power BI Desktop with the use of $expand in OData feed · Issu...

The interesting thing is that the same and similar requests were still working a few weeks ago. We haven't changed anything in our code since then. From this I think that an Excel/Power BI Desktop update may cause the issue. What do you think about this?

Also, I would add that if I first load the data without expanding and then perform the expansion and then the filtering, it works that way, but it is obviously more inconvenient for our customers.

[peterkovecses]

Hi @Anonymous ,

The Power BI Desktop version I'm using is 2.128.1177.0 64-bit from April 2024. Yes, I observe the same behavior in Excel - the data loading was functioning correctly until it unexpectedly started to malfunction from one day to the next, without any alterations being made on our part. Also, there is no specific state included in my Excel file.

The issue presented itself from one day to the next in our production application without any changes being made to the code or infrastructure. I then created a few simple demo applications with basic OData functions and observed the same incorrect behavior with any API using the current OData version. Interestingly, this feature was working correctly just a few weeks ago with this same OData version.

Here is an example of the JSON structure from one of my demo applications:

{ "@odata.context": "...", "value": [ { "Id": 1, "Title": "Clean Code", "Authors": [ { "Id": 1, "Name": "Robert C. Martin" } ] }, { "Id": 2, "Title": "The Pragmatic Programmer", "Authors": [ { "Id": 2, "Name": "Andrew Hunt" }, { "Id": 3, "Name": "David Thomas" } ] }, { "Id": 3, "Title": "Design Patterns: Elements of Reusable Object-Oriented Software", "Authors": [ { "Id": 4, "Name": "Erich Gamma" }, { "Id": 5, "Name": "Richard Helm" }, { "Id": 6, "Name": "Ralph Johnson" }, { "Id": 7, "Name": "John Vlissides" } ] }, { "Id": 4, "Title": "Introduction to the Theory of Computation", "Authors": [ { "Id": 8, "Name": "Michael Sipser" } ] }, { "Id": 5, "Title": "Refactoring: Improving the Design of Existing Code", "Authors": [ { "Id": 9, "Name": "Martin Fowler" } ] } ] }

I removed the odata.context value because I couldn't send the response with it. The full JSON can be found on the linked GitHub issue page.

I've had multiple developer colleagues test the feature with the current OData version and everyone has experienced the same issue as me. This is why I believe that this might have been caused by an update in Excel/Power BI Desktop as we've been using the same OData version that was previously working correctly.

Interestingly, I know of older OData APIs where this feature is still working correctly to this day. This makes me think that something changed in Excel/Power BI Desktop or how these applications interact with the current OData version, resulting in this incorrect behavior.

[Anonymous]

HI @peterkovecses,

I find this server seems deployed on localhost, have you applied any change to the OData server settings ? Any changes applied to the firewall rule or network policies? If that is the case, they may block the requests and affect the OData feed source usages.

Regards,

Xiaoxin Sheng

[peterkovecses]

Hi @Anonymous 
I wanted to follow up on our last conversation regarding the issue with using the $expand query in Power BI Desktop and Excel. Since my last update, I have checked the case several times. Unfortunately, the issue persists with all current versions of the OData API, including the pre-release.

To recap, here’s what we’re observing:

- When using an OData feed URL that includes an $expand query, only the expanded columns are visible in Excel and Power BI Desktop, while other columns from the main entity are not being loaded correctly.

- This issue occurs regardless of the computer or network environment, suggesting that it's not related to local settings, firewall rules, or network policies.

Importantly, this functionality previously worked correctly with the same OData package version. We have not made changes to our code or the OData configuration that could explain this behavior change.

I'm concerned that this might be related to a recent update in Excel/Power BI Desktop that affects how these applications handle OData feeds with $expand queries. This issue has significant impacts on our ability to effectively use OData feeds in our projects.

Could you please escalate this issue to your engineering team for further investigation? Any advice or workaround you can provide in the meantime would be greatly appreciated.

Thank you for your attention to this matter.

[peterkovecses]

Hi @Anonymous 
No, I use completely default settings. Currently, if I create the simplest OData API using the current OData package on any computer, I experience the same thing. And as I wrote before, the function worked correctly with the same OData package and the same program code. I experienced the same thing not only in the case of localhost, but also in the case of a deployed app.

[Anonymous]

Hi @peterkovecses ,

What version of power bi desktop are you worked? Any specific state includes in your excel file? If you change to use excel data connector, did this issue still appeared?

Please share some more detail information about this issue, they should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng