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 or nested fields can either be Records or Tables and can be expanded in Power Query Editor.

By using Power Query's native ability to transform your data, and specifically the Table.ExpandTableColumn and Table.ExpandRecordColumn, you can show and include these hidden fields in your Excel or Power bi Reports.

Steps

1. Open Power Query Editor:
   • Microsoft Power BI Users: Click on Transform Data button in Home menu.
   • Microsoft Excel Users: Click on Data > Get Data > Launch Power Query Editor.
2. A new dialog box will appear with all the Tables on the left side.
3. Navigate to the Table which contains the Record column.
   • Please note that you can find more information on sub-fields within endpoints by referring to the endpoints' documentation.
4. Record columns will be displayed with an Expand button beside the column name.
5. Upon clicking the Expand button, all the sub-fields will be displayed as a list. Select the columns 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 the table refresh is completed, you will be able to view the hidden columns in your report.

Tutorial

Here is a video from Nic explaining the process in Power BI

Click to load content

Related Topic

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