Power BI Microsoft Dynamics CRM 2013 – Part 7

Colin Maitland, 02 November 2014

In this series of blogs I am providing a simple overview of how to connect Microsoft Excel to a Microsoft Dynamics CRM Online Organisation and how to use some of the Power BI tools in Microsoft Excel. 

In my previous blog I demonstrated how to use the Power Query Editor in Microsoft Excel to reduce the columns in the AccountSet, ContactSet and OpportunitySet previously retrieved from a Microsoft Dynamics CRM Online Organisation; i.e. 

•  The AccountSet was reduced from 208 to 63 columns.
•  The ContactSet from 230 columns to 61 columns.
•  The OpportunitySet from 149 to 36 columns.
 

In this blog I will demonstrate how to expand Option Set, Lookup and Money columns and in my next blog I will show how to expand Parent/Child relationship columns. The following screenshot shows some columns from the AccountSet. Some of these columns do not yet show the values they contain and so cannot yet be used when creating Power BI related reports in Microsoft Excel: 

•  opportunity_customer_accounts is a relationship to related child Opportunities.
•  PrimaryContactId is a relationship to the related parent Primary Contact.
•  Revenue and CreditLimit are Money columns. 
•  StatusCode and StateCode are Option Set columns.
 

 

The steps to expand columns to display their Id, LogicalName, Name or Value, is straight forward: 

1.  Click the Expand  icon in the top right-hand corner of the column heading.
2.  Select the columns to expand; e.g. Id, LogicalName, Name, Value.
3.  Click OK.
 

The following screenshots demonstrate this for the PrimaryContactId parent relationship column: 

Here I have expanded the PrimaryContactId column into two columns; i.e. PrimaryContactId.Id and PrimaryContactId.Name. The Expand PrimaryContactId step is added to the list of Applied Steps. 

The following screenshots demonstrate this for the Revenue Money column. When expanded the Revenue column is replaced with a Revenue.Value column and an Expand Revenue step is added to the list of Applied Steps: 

The following screenshots demonstrate this for the StateCode and StatusCode Option Set columns. When expanded the StateCode and StatusCode columns are replaced with StateCode.Value and StatusCode.Value columns and Expand StateCode and Expand StatusCode steps are added to the list of Applied Steps: 

This image shows the Applied Steps, added so far, in the Advanced Editor: 

In my next blog I will discuss and demonstrate expanding the opportunity_customer_accounts child relationship column which has some differences when compared with expanding Option Set, Lookup and Money columns. In a subsequent blog I will then demonstrate how to transform the Option Set numeric Values into their corresponding text Labels.