Display missing data/fields in Microsoft Excel and Microsoft Power BI

OdataLink provides the data as an OData feed which is generally hierarchical or nested in nature.

By default, Microsoft Excel and Microsoft Power BI display only the header or the first-level columns and hide nested columns.

These hidden nested fields can be expanded by Transforming Tables in Power Query Editor.

Steps

To display nested columns in Microsoft Excel or Microsoft Power BI, ensure that you have selected all the endpoints required.

1. Once the required Endpoints have been selected in the Navigator dialog box, instead of the highlighted "Load" button, click on Transform Data button
2. A Power Query Editor dialog box will be displayed with all the endpoints selected in the previous step will be displayed in the "Queries" section on the left side.
3. Navigate to the query which contains the nested column.
4. Nested columns will be displayed with an Expand button beside the column name.
5. Upon clicking the Expand button, all the nested fields will be displayed as a list. Select the columns you want by clicking on the check box beside the field names.
6. Once you've expanded all the required columns, click on Close & Load button in the Home Menu

Once completed, you will be able to view the nested columns in your report.

Related Topic

Linking your data to a Standard Microsoft Power BI Report
Linking your data to a Standard Microsoft Excel Report