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 @v-shex-msft ,

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.

[v-shex-msft]

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 @v-shex-msft 
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.

[v-shex-msft]

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