Using Microsoft Power BI to access the data provided by the OData service is quick and easy. Microsoft Power BI is a great tool for creating reports and statistics.
To add the OData service to Microsoft Power BI, proceed as follows:
Aggregates, Enumerations and Lists
When you create an OData service, it is very likely that some of the required properties have aggregates or lists as data types.
Aggregates are displayed as “Record” in Microsoft Power BI and lists are displayed as “List”. In the “Transform Data” dialog you can see these aggregates and lists.
In order to make them useable, click on the “Expand” tool at the end of the column (see red box on the column in the screenshot) to choose an expanding option. Such an option is e.g. creating a new table; however, this requires additional work as relationships between the tables need to be set.
The easiest way for aggregates (or “Records” in Microsoft Power BI terms) is to simply expand the columns: new columns are inserted for the chosen fields inside the aggregate. You can use it after clicking “Close & Apply”.
As for lists, the approach chosen depends on the expected number of entries. If you can predict having only a couple of entries, expanding the list is an option. In this case, the other values are duplicated. If there are many entries, creating a new table based on the list may be a better solution.