Power BI provides several different ways to fetch data from Dynamics 365 CE and today I will share my know-how on them.
This method is mostly used to fetch data from Dynamics 365 CE. Initially, this was the only method to fetch data from CE.
To use this, go to Get Data > Online Services > Dynamics 365 (online)
Selecting the above option, you will get the following dialog box:
Please visit the following link to know how to get the above URL –
Provide your credentials here:
And you will get a list of entities, where you can either select all the entities or you can select specific entities and you will get all the data from those selected entities.
This connector was made generally available from June 2019.
To use this, go to Get Data > Online Services > Common Data Service.
You will get the following dialog box where you need to provide the URL(as retrieved in the earlier approach) for Dynamics 365 CE:
By default, ‘Reorder columns’ and ‘Add display column’ is set to true.
Click on OK and you will see two options, ‘Entities’ & ‘System’.Both these options have same number of entities but expanding Entities, you will see entities with their Display names and expanding System, you will see entities with their Schema names as below:
The difference between these two is that the previous one provides labels with values for Option-set and Status fields. Columns with the labels of Option-set fields are suffixed with “_display”. And for Lookup fields, it just brings Id for the Lookup value entity. While the latter option works exactly similar to Dynamics 365(Online) Connector. Hence, using Common Data Service connector > Entities, looks a good option.
Pros for Common Data Service connector > Entities:
Cons for Common Data Service connector > Entities:
Cons for Common Data Service connector > System:
To use this, you need to add Power Query (M) Builder plugin in XRM Toolbox.
Connect your organization in XRM Toolbox, and select the plugin. You will see the following screen:
Select Load Entities > select the entity for which the data is needed > select required fields.
After selecting the required fields, click on Update FetchXML. You will proceed to the following screen:
Select Generate FetchXml option and you will get Power Query for the selected fields.
Copy-paste the query and paste it in Blank Query and the data will be loaded along with labels for Option-set and values for Lookup fields. It also queries for the renaming the columns as Display names.
Please note that you can also use Generate OData and use OData query but this option just eliminates efforts for renaming and removing columns. You would still need to retrieve labels for Option-set fields and expand Lookup fields.
A lot depends on which approach needs the least efforts to satisfy your requirements.
Please feel free to comment down if you are aware of any other method or if you know any other limitations regarding the above-mentioned approaches.
© All Rights Reserved. Inkey Solutions 2020