Welcome back to part 2 of my review of tips and gotchas for using Power Query with Dynamics CRM OData feeds. 

• Part 1
• Time Zone Offsets
• Date Tables
• Part 2 (this post)
• Option Set Labels
• Performance

Let’s continue our review and talk about how ugly the option set numeric values are, and how you can improve your query times by tweaking (filtering more specifically) the OData URL used in Power Query.

Option Sets and Their Labels

Now this was a fun one.  If you’re used to customizing Dynamics CRM, you have likely created an Option Set (also called a pick list).  The user picks from a list of available choices for a field, but CRM stores the text value along with a corresponding integer value.  When you load an entity via the OData feed in Power Query, you get in the integer value, not the corresponding text field.  That’s not very helpful.  This goes for not just picklists, but statuses as well.  Here’s an example of a picklist called Class:

How do we get the friendly text label?  There are two main methods I’m aware of:

• Static - Use the Replace Values function in Power Query to manually replace each of the integer values with the proper text. 
• This requires converting the column to a text data type, then using Replace Values for each value you have to replace.  This is OK if you are only worrying about a couple values that you know won’t change.  But this would get tedious fast if you have a lot of values to replace, or if the values would change. 
  
• Dynamic – Use the PicklistMappingSet entity and merging queries to dynamically do the mapping for you.
• It takes a few more steps, but you never have to worry about it. 

Dynamics CRM keeps a record of all of the option sets in this entity.  While it’s old, there is an MSDN reference for this entity. 

Merging Queries

Now first let me say that I did not think of this myself, but I saw a blog from Jukka Niiranen (CRM MVP from Finland).  I’m doing something similar, but going a slightly different route.  Basically he keeps the mappings and the main data in separate tables, creates a mapping in Power Pivot, then in Power View he chooses to display the text value.  That works of course, but I found a few cases like in pivot tables I couldn’t get it to display the text.  So I decided to take the separate queries for the picklistmappings and merge them into my main table in Power Query.  This allowed me not to have to do any mappings in Power Pivot, and ensured that no matter where I chose to display the data, I would never see the integer values.

In my example, I have two fields I wanted to map:

• Billing Option
• Class

Billing Option was either Billable or Not Billable, and Class had values of Products like CRM, SharePoint, etc.  The problem was there were some duplicate integer values in Class and Billing Option.  So when you look at the PicklistMappingSet entity, I couldn’t just use one query for both sets of values since they were not unique:

You could either use the integer in CRM, or do what I did which is make a query for each field we need to map (one for Billing Option and one for Class).  To do this:

Load the PicklistMappingSet

• In Power Query, load the PicklistMappingSet and remove all columns except SourceValue, ColumnMappingId and TargetValue
  
  
  
• ColumnMappingID will show “Record”.  Click the button next to the column, uncheck Id and LogicalName, leaving only Name.
  
  
  
• I had a lot of duplicates, so under the Reduce Rows ribbon group, click Remove Duplicates.
• Now look to see if all rows in the TargetValue column are unique.  If they’re not, the Merge will fail telling you its not unique.
• Filter by the ColumnMappingID.Name column to be sure you only include values for the column you’re merging (either only Billing Option or Class, not both). 
• You can go ahead and remove the ColumnMappingId column now as well.  Highlight the column and click Remove Column.
• Be sure you UNCHECK both worksheet and data model in the Load To settings.  Save the query.

Merge the Query

Now we can take our picklistmapping values and merge them into our main content table.  Basically what we’re doing is a join between two data sets like the VLOOKUP function in Excel.  More specifically, we are going to do an Inline Merge.  This means we are doing the merge the fields from the picklistmapping table as columns into our main Time table.  You can read more details on here on support.office.com. 

• With the main query open for your table, click Merge Queries from the Combine ribbon group.
• Click on the Billing Option value column in the main table, and in the lower section, click the dropdown and choose the filtered query containing BillingOption, and click on the column that has the Target Values.  If not every row in your main table has a value for this, check the box to only include matching rows.  Click Ok.
  
  
  
• This loads a new column in your table called NewColumn, as a Table.  Click to expand, and since I only want the text, I only choose to load the BillingOption.Display column. 
• Now you have the integer and corresponding values for your option set in your table. 

At this point, we need to do the same thing for the Class field.

• Save and close the main table, and right click on your other picklist query and click Duplicate.
• Edit this query, and edit the filter.  Change the filter instead of Billing Option to be Class. 
• Save and close this query, and give it a relevant name to distinguish from your other query.
• Edit your main table query, and click Merge Queries again.  Do the same thing, except map the integer column for Class.
• Expand it, and choose the Display field only.
• Now you can hide both the integer fields by highlighting the two columns and clicking Remove Columns from the ribbon.

That’s it!  Now you’re left with no integer values and only text values.  So every time you refresh the main query, it will get the latest values and automatically map them in your table.  Sweet!  Here’s what my queries looked like:

In the very latest release of Power Query, they changed the text for load only queries to say “Connection only.”.  I forget what it used to say.  These were the relevant steps in the main query:

Query Performance

As we saw in part 1, we know that to load any data from Dynamics CRM we need to use the OData service URL (this is for CRM Online):

https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/

You load this in Power Query, you get the entire huge list of every entity in CRM, and you choose from there.  You choose an entity, and you can’t help but notice that some entities might have over 100 columns.  If I load the OpportunitySet entity, there’s 191 columns (count in the lower left corner)!  Yikes!

Now sure you can just remove all those columns after the fact, but think about it.  Power Query has to load ALL those columns, then apply your filters.  Normally you only need to work with 5 – 15 columns in an entity, so that’s definitely not very efficient.  Maybe you know you will NEVER need to worry about closed or inactive records?  Power Query has to load all of those too, them remove them.  Building a query this way can give you refresh times at 10+ minutes.  How can we improve this?  Luckily, the OData REST API supports us doing a little filtering via the URL.  That way, Power Query won’t even TRY to load all the records you don’t want.

How do we do that?  In my case, we are using CRM for time tracking.  I am loading all time tracking records and these go back many years.  I only care about from Jan 1 2014 and forward.  Also I want to limit the columns I bring in.  We can modify the URL.  In my main query, I only want to load one entity, only 10 columns I choose, and only records dating from Jan 1 2014 and forward with a default sort applied.  This would be my URL:

https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/OpportunitySet()?$select=c5_Date,column2,column3,column4,column5,column5,column6,column7,column8,column9,column10,&$filter=(c5_Date gt DateTime'2014-01-01T00:00:00')&$orderby=c5_Date desc

You can know if this works by pasting that in your web browser.  It should return an XML type of response.  Some notes about the URL:

• The columns would be their actual name, not actually column2 or column3 (be sure to be case sensitive). 
• $Select is where you choose what columns you want to load into Power Query.
• $filter applies a filter to the incoming data.  This syntax takes some testing to get the correct syntax, and you need to be sure you know what you’re filtering on (like understanding the difference in StateCode and StatusCode).
• $orderby will perform the sorting

There’s a codeplex solution you can install that will give you an OData query designer that will help with the syntax.  You can also find a few good examples of the filters here and other examples from MSDN.  And don’t try to get fancy and use $select to try and bypass the above methods to get the Display of an option set.  Say if you try like $select=Class/Value in Power Query, it will throw this:

Can’t you just hear the Pricing is Right losing horn?  You can use the values on filtering, just not select.  So on a filter, you could do this:

$filter=(StateCode/Value eq 0)

Wouldn’t that make life so much easier if $select let you get the display name instead of the integer?  Oh well.  Regardless, using these methods, my query went from about 12 minutes to about 2 minutes to refresh.  AND, the very latest Power Query update apparently includes some performance improvements as well.  From their blog:

“Query load—Performance of loading queries has improved by about 2x-3x in this release, according to our benchmarks. Queries that used to take ~10 minutes before this update, now only take between 3-4 minutes. We encourage you to try your own scenarios and let us know if you’re hitting these Performance levels.”

I hope this has made your job a little easier or made you aware of a few other issues you may not have encountered yet or offered some solutions when working with Power Query to get Dynamics CRM content.  Power Query is an amazing tool, and I strongly encourage you to try it for yourself.  This is an exciting space, especially when the new announcements in the new Power BI Dashboards Preview automatically generate awesome dashboards with CRM Online data. 

For more information about this blog, Power Query, Power BI, Dynamics CRM or C5 Insight, contact us here!