The Power Query OData.Feed function has an option called IncludeAnnotations that allows you to return annotation values from an OData data source. It’s not obvious how to use it though – even if you use this option when connecting, you won’t see the annotation values by default because they are returned as metadata. Here’s an example of how to get annotation values using some simple M code.
The following query gets data from the statuscode column of the bookableresourcecategories table in Dynamics CRM via OData:
let
Source =
OData.Feed(
"https://xyz.crm.dynamics.com/api/data/v9.1",
null,
[Implementation = "2.0"]),
bookableresourcecategories_table =
Source{[
Name = "bookableresourcecategories",
Signature = "table"
]}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
bookableresourcecategories_table,
{"statuscode"})
in
#"Removed Other Columns"
To get the option set labels associated with these values, you first of all need to edit the record in the third parameter of OData.Feed and use the IncludeAnnotations option to get the FormattedValue annotation like so:
OData.Feed(
"https://xyz.crm.dynamics.com/api/data/v9.1",
null,
[Implementation="2.0",
IncludeAnnotations="OData.Community.Display.V1.FormattedValue"]
)
Then you need to add a custom column in the Power Query Editor that gets the metadata from each cell in the statuscode column using the Value.Metadata function:
Value.Metadata(
[statuscode]
)[OData.Community.Display.V1.FormattedValue]?
By the way, if you’re wondering what the question mark does in this expression, it stops an error occurring if there is no OData.Community.Display.V1.FormattedValue field in the metadata record; this post has more details.
The full query looks like this:
let
Source =
OData.Feed(
"https://xyz.crm.dynamics.com/api/data/v9.1",
null,
[Implementation = "2.0",
IncludeAnnotations =
"OData.Community.Display.V1.FormattedValue"]
),
bookableresourcecategories_table =
Source{
[Name = "bookableresourcecategories",
Signature = "table"]
}[Data],
#"Removed Other Columns" =
Table.SelectColumns(
bookableresourcecategories_table,
{"statuscode"}
),
#"Added Custom" =
Table.AddColumn(
#"Removed Other Columns",
"Custom",
each Value.Metadata(
[statuscode]
)[OData.Community.Display.V1.FormattedValue]?
)
in
#"Added Custom"
…and here’s the output of the query showing the option set labels in a new column:
[Thanks again to Matt Masson for providing this information]