Expanding a single list from an OData source with multiple types

[longhorntaco]

I have an OData feed that I'm injesting into PBI Desktop.  One of those fields is a collection of Events.  From /odata/$metadata:

<EntityType Name="Interaction" BaseType="MyApp.Entity">

     ......
     <Property Name="Events" Type="Collection(MyApp.Event)" Nullable="false"/>

     ......
</EntityType>

The MyApp.Event type is just a common base class.  The items in that collection are actually derived classes of MyApp.Event.  When viewing the raw data from the odata feed, I see all the fields are available for the different types.  However, when I attempt to expand the column in PBI, it only gives me the fields on the MyApp.Event type to select.  None of the other fields from the derived classes are available.  I suppose this makes complete sense, but is there a way for me to get at the other fields on the derived classes, since that data is actually there?

**Update:**

I found the Advanced Editor and was able to add my missing fields.

    #"Expanded Events1" = Table.ExpandRecordColumn(#"Expanded Events", "Events", {"DefinitionId", "Url"}, {"DefinitionId", "Url"}),

I added the "Url" field.

However, the field is always returning null.  If I open Fiddler and watch the queries/traffic when refreshing the data, I can see that data in that field is indeed getting returned by the odata feed, PBI just isn't picking it up.

Thoughts?

Thanks!

[v-yuezhe-msft]

@longhorntaco,

Are you able to get required data when connecting to your data source in other applications such as Excel?

Regards,
Lydia

[longhorntaco]

When I attempt to connect through Excel, I get the following error:

However, retrieving the data directly from the URL (in a browser) returns the proper data.  Accessing the data from Fiddler or Postman returns the proper data.  Accessing the data from code (C# console app) returns the proper data.

Here's an example of the raw JSON output from the OData feed:

I understand why the OOTB UI in PowerBI doesn't let me select the extra columns that aren't always there, but there must be a way to get that data through some other means in PBI?

[v-yuezhe-msft]

@longhorntaco,

I can't test your scenario from my side. Is there any possibility that you can export JSON data from the OData feed and connect to the JSON data using JSON connector in Power BI Desktop?

Regards,
Lydia