Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The metadata shows it as a property (https://xxxx.crm.dynamics.com/api/data/v9.1/$metadata), so it looks like PowerBi is not bringing it in. I have included an excerpt of the metadata document below, as well as an example of the json (excerpt)
What is odd is that under system, if I look at the "territories" node, it does have the parent territory data, its just the node for "Territory" under "Entities" that is missing the parentTerritoryId. ParentTerritoryId is a microsoft core field (though I believe it got added recently). Because of this, it feels like, its most likely missing as its not part of the CDM.Territory definition (maybe?).
The following shows how I can get at the data via the system.territories node. But it would be nice to be able to access this data directly via the Entities.Territory node.
let
Source = Cds.Entities("https://xxxx.crm.dynamics.com/api/data/v9.1", [ReorderColumns=null, UseFormattedValue=null]),
system = Source{[Group="system"]}[Data],
territories_table = system{[Name="territories",Signature="table"]}[Data],
#"Expanded parentterritoryid" = Table.ExpandRecordColumn(territories_table, "parentterritoryid", {"territoryid"}, {"parentterritoryid.territoryid"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded parentterritoryid",{"parentterritoryid.territoryid", "territoryid"})
in
#"Removed Other Columns"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.