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:
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:
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.
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.
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:
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.
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!
Trackback from C5 Insight Blog
Welcome back to part 2 of my review of tips and gotchas for using Power Query with Dynamics CRM OData ...
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.