Power BI Microsoft Dynamics CRM 2013 – Part 12

Colin Maitland, 12 November 2014

This blog is Part 12, in a series on creating reports using PowerBI tools such as PowerPivot, Power View and Power Map in Microsoft Excel for a Microsoft Dynamics CRM Online Organisation.

In this blog and the next I will demonstrate how to transform the Option Set column Values into their corresponding Option Set Labels using two different methods, i.e. Replace and Add Custom Column. 

In this blog I will demonstrate using Replace for the StateCode and StatusCode OptionSet columns in the OpportunitySet.

Using the Replace method, Option Set values can be replaced by adding corresponding Replaced Value steps to the list of Applied Steps; e.g. for StateCode, 0 can be replaced with Active and 1 can be replaced with Inactive.

1. Collate a list of the Option Set Values and their corresponding Labels. This can be done by looking at the Option Set configurations in the Microsoft Dynamics CRM Organisation under Settings, Customisations etc.

2. Change the Data Type of each Option Set column from Decimal Number to Text using one of the following methods:

•  Select the Option Set column and then select Text from the Data Type drop-down menu on the Transform tab of the ribbon bar.

•  Right-click on the Option Set column heading and then select Text from Change Type submenu on the drop-down menu.

Completion of this step results in a Changed Type step being added to the list of Applied Steps. For clarity I have also renamed the Changed Type step to Changed Type – Option Sets to Text.

3. Add a Replaced Value step to the list of Applied Steps for each Option Set Value using one of the following methods.

It is important to repeat this step for each and every possible value the Option Set contains, even if those values are not currently displayed or used, to ensure every Option Set value will be replaced if used.

•  Select the Option Set column and then select Replace Values form either the Home or Transform tab on the ribbon bar.

•  Right-Click on a specific cell containing the value to be replaced in the Option Set column and then select Replace Values… form the pop-up menu:

In the Replace Values dialog, if required, enter the Value To Find and Replace With, select Match enter cell contents and click OK. The Match entire cell contents option is not selected by default but selecting this option will provides a performance optimisation.

Completion of this step adds a Replaced Value step to the list of Applied Steps for each individual Option Set Value replacement. For clarity, I have also renamed each of these steps; e.g. from Replaced Value to Replaced Value – StateCode – Open, Replaced Value – StateCode – Won and Replaced Value – StateCode – Lost etc.

•  You can also use the Advanced Editor to add the Changed Type and Replaced Value steps:

The following screenshots show the result of adding the required Replace Value steps for the StateCode and StatusCode Option Set columns in the OpportunitySet:

In my next blog I will demonstrate and issue that occurs when using Replace too many times and how this can be resolved by using Add Custom Column.