Power BI Microsoft Dynamics CRM 2013 – Part 6

Colin Maitland, 22 October 2014

In this blog I will demonstrate how to reduce the number of columns in the Microsoft Dynamics CRM Online AccountSet, ContactSet and OpportunitySet OData Sets previously loaded into the Data Model in Microsoft Excel. This is an important initial step in the preparation of the OData Set queries for use in Power BI related reports. Completion of this step reduces clutter by removing unwanted columns, such as the base currency fields, and decreases the volume of data to be loaded and processed. This results in improved user focus on the information that is important and improves system performance. 

In my previous blog I demonstrated how to access the Power Query Editor. From the Query Editor. Using the Query Editor we can remove unwanted columns from each OData Set. It is important to be aware of the fact that once columns are removed from the OData Set query, they cannot be restored. Therefore take care with this step! I perform this step prior to all other steps, such as expanding reordering or renaming columns. 

The OData Sets contains the following three main types of columns: 

•  Columns from the source entity such as Account; e.g. Name:




•  Parent relationship columns which when expanded provide access to the columns from a related parent entity such as the Primary Contact; i.e. account_primary_contact; not to be confused with PrimaryContactId which is a source entity column: 

•  Child relationship columns which when expanded provide access to the columns and aggregated values from child entities such as related Opportunities; e.g. opportunity_customer_accounts:  

There are a couple of methods by which columns can be removed. These are: Choose Columns and Remove Columns displayed on the Home tab of the Query Editor ribbon bar and Remove and Remove Other Columns options displayed when you right-click on any column heading to display the popup menu or click on Remove Columns to display the drop-down menu. 

The Choose Columns action displays a Choose Columns dialog from which you can select the columns you want to keep. All columns not selected will be removed. The Choose Columns dialogue also contains a Search Columns control that allows you to search for specific columns when making your selections.



Using Choose Columns adds a Removed Other Columns step to the list of Applied Steps: 

The Remove Columns action requires you to first select the columns form the Query Editor Worksheet and then click Remove Columns. Remove Columns provides two options: Remove Columns which removes all the selected columns and Remove Other Columns which removes all the other columns. Take care with these options! It is easy to accidently select Remove Columns rather than Remove Other Columns and vice versa. 

Using Remove Columns adds either a Removed Columns or a Removed Other Columns step to the list of Applied Steps depending on the option you selected.

If you select the wrong option you can simply click X to delete the Applied Step but you should do this before choosing to Close & Load on the Query Editor menu and before adding any subsequent Applied Steps. The Query Editor menu also contains a Discard & Close option. 



In addition to using the actions on the ribbon bar to apply steps to the OData Set queries you can also use the Advanced Editor, accessed from the View tab of the Query Editor, to view and directly add, edit and delete Applied Steps: 

I have chosen to remove all AccountSet columns except for the following: 

I have chosen to remove all ContactSet columns except for the following: 

I have chosen to remove all OpportunitySet columns except for the following: 

You can remove all the unwanted columns in one Applied Step or you can use multiple Applied Steps. I prefer to use one Applied Step to remove all unwanted columns. This reduces the number of Applied Steps that have to be processed and also reduces clutter in the list of Applied Steps. 

In my next blog I will demonstrate how to Expand the Option Set, Lookup, Money and parent/child relationship related columns want to use in Power BI related reports.