Power BI Microsoft Dynamics CRM 2013 – Part 14

Colin Maitland, 27 November 2014

In this blog on this series on using the Power BI tools in Microsoft Excel to analyse and report on information retrieved from a Microsoft Dynamics CRM Online Organisation I will demonstrate how to transform Date/Time columns in the ODataSets. In my previous blog I demonstrated how to transform the Option Set Value columns.

In this example I will use the OpportunitySet which has the following Date/Time columns:

To transform these, so that they only display the Date rather than Date and Time:

1.  For each Date/Time column simply right-click on the column heading and then select Change Type, Date from the pop-up menu.

 

I also want to have a set of columns that display only the Year for use in the reports to be developed. The steps are as follows:

1.  Duplicate the Date columns: For each column right-click on the column heading and select Duplicate Column from the pop-up menu.

2.  Transform the duplicated columns from Date to Year: For each column right-click on the column heading and select Transform, Year from the pop-up menu.

 

3.  Rename the duplicated columns: For each column right-click on the column heading and select Rename … from the pop-up menu, then rename the column.

The completion of all these Date/Time and Date column related actions results in the following steps being added to the list of Applied Steps. I have renamed each of these steps for clarity; e.g. From Duplicated Column to Duplicated Column – CreatedOn etc.

An alternative to adding a Renamed Columns step after the Duplicated Column steps, is to edit the Duplicated Column steps using the Advanced Editor to specify the name such as CreatedOnYear rather than Copy of CreatedOn etc. as part of the Duplicated Column step.

•  Here are the automatically created steps in the Advanced Editor:

•  Here are the manually edited steps in the Advanced Editor. In this case the Renamed Columns step is not required because the Duplicated Column steps have been edited to name each duplicated column with the required name.

 

Something to be aware of with the steps in the Advanced Editor is that every step references the previous step and the last step does not end with a comma. This example shows the To Year step which references the preceding Duplicated Column step.

In my next blog I will summarise the steps completed so far and demonstrate some final steps for preparing the AccountOpportunitySet, AccountSet, ContactSet and OpportunitySet examples to be used in the reports to be developed using the Power BI tools in Microsoft Excel.

In subsequent blogs I will demonstrate how to create the reports similar to those shown in the first blog of this series.