Power BI Microsoft Dynamics CRM 2013 – Part 10

Colin Maitland, 05 November 2014

In this series of blogs on using Power BI tools in Microsoft Excel to work with ODataSets downloaded form a Microsoft Dynamics CRM Online Organisation, I have so far demonstrated how to connect to the Microsoft Dynamics CRM Online Organisation; select one or more ODataSets, such as AccountSet, ContactSet and OpportunitySet; configure relationships between the ODataSets;  reduce the number of columns in each ODataSet and expand Option Set, Lookup, Money and Parent/Child relationship columns.

In this blogs, and the next, I will demonstrate how to reduce the number of rows and how to sort the rows in the Microsoft Dynamics CRM Online ODataSets using the Power Query Editor in Microsoft Excel.

Reducing Rows using Keep and Remove

In the Query Editor there are options for Reducing Rows using Keep Top Rows, Remove Bottom Rows, Remove Duplicates and Remove Errors.

•  Keep Top Rows allows you to select how many of the Top N Rows to keep. This adds a Kept First Rows step to the list of Applied Steps. This example would remove all rows except for the top 10 rows:

 

•  Keep Bottom Rows allows you specify how many of the Bottom N Rows to keep. This adds a Kept Last Rows step to the list of applied steps. This example would remove all rows except for the bottom 10 rows: 

 

•  Keep Range of Rows allows you to specify how many rows to keep starting from a specific row. This adds a Row Range step to the list of Applied Steps. This example would remove rows 1 to 10:

•  Remove Top Rows allows you to specify how many of the Top N Rows to remove. This adds a Removed Top Rows step to the list of Applied Steps. This example would remove the top 10 rows:

•  Remove Bottom Rows allows you to specify how many of the Bottom N Rows to remove. This adds a Removed Rows step to the list of Applied Steps. This example would remove the bottom 10 rows:

•  Remove Alternate Rows allows you to specify a pattern of rows to be remove and keep. This adds an Alternate Rows step to the list of Applied Steps. This example would remove every second block of ten rows:

•  Remove Duplicates removes rows with duplicated values in the currently selected columns. This adds a Removed Duplicates step to the list of Applied Steps.

•  Remove Errors removes or keeps all rows with errors in the currently selected columns. This adds Removed Errors or Kept Errors steps to the list of Applied Steps.

Reducing Rows by Filtering

You can also Reduce Rows by applying one or more Filters. Applying Filters add Filtered Rows steps to the list of Applied Steps.

For instance to remove Accounts with no related Opportunity, simply click the drop-down icon in the top right-hand corner of the opportunity.accounts.name and then unselect the Name columns with a (null) value. The rows that are not selected will be filtered out.

Depending on the column type you can also apply Filters using a range of Date and Time, Logical, Number and Text operators such as those shown in the following screenshots:

In my next blog I will briefly describe the options for sorting records.