Using Microsoft Power BI Desktop to build Dynamics CRM Online reports Part 2

In the previous post I showed a very simple example of how you can create a report in Power BI Desktop – in this post I will show you how to extend this simple example. First we will create a new dataset based on Opportunity data in Dynamics CRM Online but we will add extra columns by specifying them in the oData query https://[yourtenantname].crm4.dynamics.com/xrmservices/2011/OrganizationData.svc/OpportunitySet?$select=CustomerId,EstimatedValue,SalesStage Next we will expand the columns in the same way that we did in Part 1.

If you have worked with Dynamics CRM you will probably know the concept of an Option Set (a.k.a pick list) – which allows to list a set of available choices for a specific field. Dynamics CRM will store the integer value (not the label) within its database. SalesStage is an example of such an Option Set and you notice that the integer value is also exposed in the OData query.

There are two ways of getting the labels back for the Option Set – one is simply using “Replace Values” function from Power BI. The other option, is a more dynamic method using the PickListMappingSet as outlined in Gotchas when using Power Query to retrieve Dynamics CRM Data – Part 2 – here is a brief summary of the steps:

• Retrieve the PickListMappingSet and  expand the ColumnMappingId column
• Duplicate the PickListMappingSet and rename it to SalesStage (given it the name of the Option Set makes the whole more understandable)
• Filter the ColumnMappingId.Name column to only include SalesStage values

• Finally merge the values of the SalesStage data source with the OpportunitySet data source by selecting Merge Queries (in the Combine section). You will probably notice that not all of the rows can be matched – this is because some of the records contain null values – you should decide up front what how you are going to clean up your data for these types of input errors. Another thing to keep in mind is the fact that option sets that you create yourself are not exposed in the PickListMappingSet – so this requires you to do “Replace Values”. You can vote on Connect for Make user created option sets also available through the PickListMappingSet odata table

• After the merge a new column is added of type “Table”, click to expand and keep the “SourceValue” column

Finally, I grouped the columns on CustomerId and SalesStage, sorted by estimed revenue and filtered to keep the top 50 rows. Next I used a simple bar chart to display the data – I also played around with the colors of the data labels – also check out the references listed below for some helpful links about color formatting.

References:

• Using Microsoft Power BI Desktop to build Dynamics CRM Online Reports Part 1
• Getting started with color formatting and axis properties in Microsoft Power BI
• Tips and tricks for color formatting in Power BI

Tags van Technorati: Power+BI,Microsoft,Visualization,analytics,Dynamics+CRM,CRM