Power Query(M) Builder XrmToolBox Plugin developed by Ulrik Carlsson and Mohamed Rasheed makes it very easy to develop Power BI Reports targeting Dynamics 365 CE.
Just listing down the basic steps below for quick reference à
Install the Power Query (M) Builder plugin from XrmToolBox
Click on Load Entities and select the Entity against which we want to write the report. Here we will be writing a report against Case entity (for e.g. Cases by Origin), so let us select the Case entity. For selecting the fields either we can make use of view or search and add the corresponding fields in Selected Fields Section.
Here we have selected the following 3 fields
Click on Update FetchXML button.
This opens the FetchXML tab, where we can select the fields (note we have formatted value field added for Origin OptionSet field) and also select to add record URL or to use all the attributes of the entity.
Here we have selected all the fields and also checked the option “Add Record URL”.
Click on Generate FetchXML, to generate the query.
The generated main query
Now back in Power BI Desktop, open the query editor and create a new blank query with following name and value
Dyn365CEBaseURL | https://[org].crm.dynamics.com i.e. URL of the CRM Organization. |
We can use the Generate Service URLs button to get this URL from within the plugin.
Similarly create a new blank query as
ServiceRootURL | https:/[org].crm.dynamics.com/api/data/v9.0 |
Get the value from the ServiceRoolURL tab from within the plugin.
Create one more Blank Query and click on Advanced Editor and copy the main query generated earlier and paste it.
Click on Done.
We can see the result generated for us in the Query Editor window
Click on Close and Apply
Back in report editor select the fields or any other visualizations to create the report
To format the URL, select the Link field and go to Modelling tab and in Data Category select Web URL to enable the links.
Thus, we are done with our report. Next steps would be to publish them, add them in dashboard, Embed them within Dynamics CE, Refresh them etc..
Hope it helps..
3 thoughts on “Creating Power BI Report using Power Query (M) Builder plugin of XrmToolBox”