Forum Discussion
Advocate IICan Power BI access Dynamics CRM saved queries?
Advocate II@Tom - RE: "Power Query will give you the expand option "
Would you please clarify that? I'm in PowerBI Desktop, and the Fields listed do not include any fields defined as Option Sets
Anonymous Most of my entities are custom, so I don't think Solution Templates will help me
When you select a field that is an option set or a lookup value through oData through Power Query it should atomatically give you the option to get the values (if it's an option set) or the lables if it's a lookup value.
I have a simple odata url that pulls StatusCode from Opportunity:
.../xrmservices/2011/OrganizationData.svc/OpportunitySet?$select=StatusCode
When that appears in the Query Editor you get the option to expand the record:
into the option set values:
- dennisr
OK, I found the 'Expand'. However, it's a pain to have to expand dozens of fields. It seems that doing an expand has Power BI desktop make a metadata query back to Dynamics, which takes a long time - 40-50 seconds per field (!), since they need to be done one at a time. Clearly, there needs to be a setting in Power BI that automatically does the expand and selects the Value or Label, and/or the ability to expand multiple fields at the same time.
(Note, that whe you use FetchXml with the SDK, it does automatically get the underlying value/label. )