Power BI Microsoft Dynamics CRM 2013 – Part 13

Colin Maitland, 23 November 2014

This blog is Part 13, 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. The emphasis in this series so far has mostly been on preparing the oDataSet queries. These are important foundational steps to be completed prior to building reports.

In my previous blog I demonstrated how to transform Option Set column Values into their corresponding Option Set Labels using Replace. In this blog I will demonstrate how to transform Option Set column Values into their corresponding Option Set Labels using Add Custom Column. I will also demonstrate an issue encountered when using Replace too many times and how this is resolved by using Add Custom Column instead of Replace. 

If too many Replaced Value steps are added to the Query, i.e. more than 5 to 10, the refresh of the Preview or Load of data takes minutes to complete; even for a very small number of records. During refresh the CPU performance remains high, e.g. from 80% to 100%, as shown in the following images which demonstrate this issue for the AccountSet after using Replace for the CustomerTypeCode, PreferredMethodOfContact, StateCode and StatusCode Option Set columns.

Using Add Custom Column instead of Replace resolved this issue. The following demonstrates the steps for adding Add Custom Column for the Industry Option Set column:

1.  Select Add Custom Column from the Add Column tab on the Query Editor ribbon bar.

2.  From the Add Custom Column dialog provide a Name for the column; e.g. Industry.

3.  Add the following if-then-else formula into the Custom Column Formula field and then click OK to close the Add Custom Column dialog. Notice the last else [IndustryCode.Value] clause. It is important that the Value is displayed if there was no match found in any preceding if-then-else clause.

This results in an Add Custom step being added to the list of Applied Steps. In my example I have renamed this step to Added Custom – Industry to provide clarity.

The following image shows the original Industry.Value column and the custom Industry column which is based on the values in the Industry.Value column. Unlike with using Replace, there is no need to change the Data Type of the Option Set column from Decimal Number to Text prior to transforming it.

After adding a custom Column that displays the text Labels rather than the numeric Values for Industry you may then optionally choose to remove the IndustryCode.Value column.

You should not insert the Remove step for the Industry.Value column prior to the related Add Custom Column step in the list of Applied Steps as this action will cause the Add Custom Column step to fail as shown in the following image. You should also not remove the Industry.Value column prior to adding the Add Custom Column step as that will cause the Industry.Value column to be unavailable for you to use when adding the Add Custom Column step. Remember also that once you remove a column it cannot be re-added, even if you delete the Remove step from the list of Applied Steps.

The following image shows the list of Applied Steps with the Added Custom – Industry and Removed Columns – Industry steps.

My approach to query design is to add all the required Added Custom steps first, e.g. one for each Option Set column to be transformed from numeric Values to text Labels and then add a single Removed Columns step, as shown in the following image, that removes each of the Option Set columns that were transformed using Add Custom Column. This reduces the overall number of Applied Steps.

In my next blog in this series I will demonstrate how to transform the Date/Time fields so that they can be used in the reports that will be used using the Power BI tools in Microsoft Excel.