Access the OData Service via Microsoft Power BI

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.

For more details on how to work with Microsoft Power BI please refer to: https://powerbi.microsoft.com/en-us/support/: new window

To add the OData service to Microsoft Power BI, proceed as follows:

1. On the “Home” tab, click the “Get data” button, and then click “OData feed” as data source.
   
2. Enter the URL of the OData service (see chapter “Creating an OData Service”) in the URL field and click “OK”.
   Note: The “Basic” connection option is sufficient.
   
   Since OData usage requires authentication you need to select the “Basic” authentication option. Enter your e-mail address for the cloud log-in as the user name and the generated Password for Applications as the password (see chapter “Creating a Password for Applications”).
3. Click “Connect”.
   
4. After successful authentication, you can select from several display options in the “Navigator”. Select your desired options and click the “Load” button.
   

Lists

When you create an OData service, it is very likely that some of the required properties have lists as data types.

Lists are displayed as “List”. In the “Transform Data” dialog you can see these 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.

For lists, the approach to make them useable 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.