View source for 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 [[Field Type: Record|Records]] or [[Field Type: Table|Tables]] and can be expanded in '''Power Query Editor'''.

By using Power Query's native ability to transform your data, and specifically the [https://learn.microsoft.com/en-us/powerquery-m/table-expandtablecolumn Table.ExpandTableColumn] and [https://learn.microsoft.com/en-us/powerquery-m/table-expandrecordcolumn Table.ExpandRecordColumn], you can show and include these hidden fields in your Excel or Power bi Reports.

= Steps =
<!--
[[File:Excel_ODataFeed_URL.png|300px|thumb|right|Enter the OData feed url]]
[[File:Excel_ODataFeed_Login.png|300px|thumb|right|Enter the login details]]
[[File:Excel_ODataFeed_Endpoint.png|300px|thumb|right|Select the endpoint]]
-->

# 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'''.
# A new dialog box will appear with all the Tables on the left side.
# Navigate to the Table which contains the [[Field Type: Record|Record]] column.
#* ''Please note that you can find more information on sub-fields within endpoints by referring to the endpoints' documentation.
# [[Field Type: Record|Record]] columns will be displayed with an '''Expand''' button beside the column name.
# 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.
# 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
<br />

{{#ev:youtube|lE2nPwS1lXA|dimensions=720x480}}

= Related Topic =

[[Linking your data to a Standard Microsoft Power BI Report]]<br />
[[Linking your data to a Standard Microsoft Excel Report]]<br />