OdataLink provides the data as an OData feeds which are naturally hierarchical or nested in nature.
In Power Query, only the header (first level records) are displayed by default. For example, if you are wanting to extract invoices, only the Invoice date, invoice number, etc. will be returned and the invoice lines will be missing. In order to view those nested fields they first need to be expanded in Power Query. This article goes through how to make those missing fields visible.
Most columns of data within Power Query will contain discrete values (e.g. an amount, a date, some text).
However, there are some specialised fields that are more complex and provide data as either records or tables.
Record fields are specialised fields that contains other fields. For instance, an Account field could contain an Account Number and Account Name fields.
Table fields are specialised fields that contains multiple records. For instance, on an invoice endpoint, a Lines field could contain multiple transaction lines. In turn, each transaction line could be made up of other fields such as Account, Job, Tracking Categories, Amounts, etc.
These nested fields, while visible within Power Query, will become hidden within Power BI and Excel as they cannot be charted or used directly. Instead, they need to be transformed using an Expand Operation.
You can see it in explained and in action in the following video. You can also download the accompanying Power BI file used in the video.
To expand these fields get to the underlying fields within these specialised fields, you can:
- Edit the query within Power Query.
- Click on the dropdown next to the column name.
- Tick the fields you want to include and add to your data set.
Expanding Record fields will simply add the sub fields to your data set.
Expanding Table Fields, on the other hand will not only add the sub fields to your data set, but it may also duplicate every record you previously had. This is because you may have multiple records within each table. When you expand Table Fields, Power Query will repeat the values of the original columns for each record in your expanded table.
When you expand Table Fields, it is important that you do not use any amount fields that were present at a higher level. Otherwise, you will duplicate the values and amounts on your reports and charts.