If you have been following the Excel business intelligence space lately, you are familiar with all of the slick add-ins Microsoft has introduced with Power Query, Power Pivot and Power Map.  We work with a lot of Dynamics CRM clients, and I recently set out to use some CRM Online data to create some visualizations and dashboards in Excel.  I encountered a few different situations I had to deal with, so in this post I want to walk you through some of the gotchas I found when you use Dynamics CRM data with Power Query.  This will be a 2 part series:

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

Let’s face it, Power Query is one awesome (and free) tool.  It’s extremely flexible, and is constantly being extended and improved by Microsoft.  One of those latest improvements (released days ago actually) is the inclusion of a proper data source for CRM Online.  Up to now, you just used the OData source.  There are other blogs that walk you through the process of loading CRM data into Power Query so I won’t list them here as that’s not the point of this blog.  But as a refresher, here’s a quick overview:

• You get the “Organizational Data Service” (or OData) REST URL from the Customization page in the Settings entity.  It will be something like this (CRM online):
• https://<tenant>.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/
• Open Power Query, choose From Other Sources, choose From Dynamics CRM Online
• Paste the URL
• login as desired
• Choose your entities to import
• Modify the query, expanding the columns, filtering and removing columns as desired
• Creating relationships in Power Pivot as needed, date tables
• Creating visualizations in Power View, pivot tables or Power Map
• If you have Office 365, publish the Excel file to Power BI and enable in Q&A

The gotchas I list below are for the most part in the bolded configurations you need to make Power Query above to get the data to be accurate and usable. 

Date Tables, Time Zones and Everything In Between

There are two separate but related issues dealing with dates, one specific to CRM data and that might apply to other data sources as well. 

Dealing With Time Zones / Universal Time (UTC)

Why do we care about this?  You haven’t had to before, because you never connected to the CRM database data before (unless programmatically with plugins or the like).  When you go to a view in CRM and export to Excel, the friendly names and local time zone dates for the records will be used on the export.  This may be fine for small purposes, but it requires the Outlook add-in if you want to make a dynamic export, which I don’t like and it for sure will not be able to be refreshable.  So we turn to Power Query.  When you have a date field, what you don’t know is that the date is actually stored in UTC format in the CRM database.  So when you see the date for records in Power Query, they do not have any time zone offsets applied, which can cause havoc in your data.  When you look at date fields in the CRM UI, it will automatically convert that date/time to your local timezone as define in your CRM options. 

To get the same in Power Query, we need to do a couple steps to be sure you get the date/time in your local time zone:

• First thing is to convert the raw date column to be in the UTC timezone format. 
  • Click on the column name header.
  • From the ribbon, for Data Type, choose Date/Time/Timezone.
  • Rename it to something like UTCDate, anything that denotes it’s the raw universal time.
    
    
    
• Add a calculated column to the date in the format we need, applying the proper time zone offset using the DateTimeZone.ToLocal Power Query function (you can see other functions here)
• Click Add column tab on the ribbon, and click the Date button in the From Date & Time group, and choose Date Only
  
  
  
• Now we need to convert this to my local time zone (US EST in my case).  To do that, we modify the Power Query formula.  Click in the expression box.  It should look something like this:
• = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([CreatedOn]), type date)
• Replace DateTime.Date (bolded) with DateTimeZone.ToLocal (case matters so type exactly).  The whole formula should look like this:
• = Table.AddColumn(#"Changed Type", "Date", each DateTimeZone.ToLocal([CreatedOn]), type date)
  
  
• This should create a new column, based on the CreatedOn field that with the local time zone offset applied, like so:
  
  
  
• Now format the new column as you see fit, like if you just want the date.  Click the Data Type again, and choose Date.  Now you will just the date with no time or zone.

I highlighted in yellow examples of where you could potentially get into problems.  See the day shift?  if you looked at the Created Date on a record in the UI, you would see the date in the right column.  It throws it off by a whole day!  Normally that’s not a big deal, but say for example you’re using cases, or doing time tracking or billing.  The Dates have to be EXACT, and you can’t risk the data being off. 

Date Tables

While we’re on the subject of dates, I thought I would bring up date tables.  You may not need one, but if you plan to analyze related data over time you certainly will (need by year, quarter, week, etc).  This doesn’t apply to Power Query per say, but comes into play when you start building your data model in Power Pivot.  Microsoft has a decent introduction write up here.  You can either create a date table by hand, or you can import one!  You can get exact steps from PowerPivot Pro that outline how to download it for free from the Azure DataMarket. 

Let me switch back to Power Query for a second.  If you do have one table needs and don’t need a date table, you can get some basic date table functionality straight in Power Query (and age with the latest update).  When you add a calculated column, note the available options for time:

You can’t get ALL of the same values like a date table, but for simplistic needs these might suffice.  And the best part is you don’t have to write a single function!  Who doesn’t like a mouse click?

Stay tuned for part 2 where we dive into translating option set (picklist) integers into text values, and discuss some ways to increase your query performance.

For more information about this blog or C5 Insight, contact us here!