Power BI Microsoft Dynamics CRM 2013 – Part 16

Colin Maitland, 10 December 2014

In my previous blogs in this series I have demonstrated how to connect Microsoft Excel 2013 Professional Plus to a Microsoft Dynamics CRM Online Organisation, download Account, Contact and Opportunity ODataSets and update those ODataSets using Power Query so that they are ready to be used for developing reports using the Power BI tools in Microsoft Excel such as PowerPivot, Power View and Power Map.

In this blog I will demonstrate how to reorder the ODataSet columns. This is an important step to complete because it makes the ODataSet queries easier to use by yourself and by others when building reports.

The columns in the ODataSets can be reordered by one or both of two methods:

Method 1: Drag and Drop

Simply selecting one or more columns and then, while holding the left mouse button down, drag the selected columns to the left or to the right to their new location. In the following example the StateCode.Value column has been moved to be before the StatusCode.Value column:

The process of moving columns this way can be slow however if the screen has to scroll to the left or to the right in order to display the location that the selected columns are being move to.

Method 2: Advanced Editor

An alternative approach is to use the Advanced Editor to either add or edit a Reordered Columns step as shown by the following example of a Reordered Columns step that has been manually edited using the Advanced Editor.

Note: The reference to “#To Year – Duplicated Date Columns” is a reference to the previous step. All steps reference the previous step in the list of Applied Steps. If you manually reorder steps in the Advanced Editor you will also need to update this reference in the step being moved, the step that follows the step being moved after it is moved, and the step that followed the step being moved after it was moved.

The following screenshot shows a preview of the first few columns of the Contact ODataSet before and after being reordered.

In my next blog I will demonstrate the final step to be completed before using the ODataSets for building reports using the Power BI tools in Microsoft Excel.