Quick Tip – Connecting Power Query for Excel to Dynamics CRM

If you’re a power user of Dynamics CRM you’ve probably at some point exported data from a view or advanced find to a static or dynamic Excel file. This comes in extremely handy when you need to perform some analytics or advanced filtering on a set of data that goes beyond what CRM can do out of the box. This works well of course for the data you are allowed export, but in some cases (like Notes) you aren’t able to actually export as the option doesn’t appear in the user interface.

If you are an On Premise customer you have the option to connect directly to the database (with caution) and create a SQL query to read the data but if you’re running CRM Online you know this isn’t exactly an option. One thing you can do (On Premise or Online) to get access to this data is it use Power Query for Excel to access your CRM organization’s OData feed and bring the information directly into Excel to work with it.

To get started you’ll need to first download and install the add-in for Excel 2010 or 2013, once you’ve done so you should see a new tab in Excel labeled POWER QUERY. In order to connect to CRM you’ll need to look under the Get External Data section in the ribbon and then chose From Other Sources. You’ll want to select From OData Feed option.

Here you’ll be prompted for a URL. To find the correct value you’ll need to go into CRM and navigate to Settings -> Customizations -> Developer Resources.

The value you’re looking for is under the Organization Data Service heading (OData/REST).

Next you’ll need to authenticate to your organization. For On Premise users you can select Windows authentication and for Online users select Organizational Account. Online users will need to sign in here and provide their credentials. Once the selections are made you’ll need to Save in order to proceed.

Once you’re logged in you’ll see a list of data sets that CRM makes available under the hood.

In a future blog post we’ll take a look a few things that you can do with Power Query.

Download: Microsoft Power Query for Excel

The post Quick Tip – Connecting Power Query for Excel to Dynamics CRM appeared first on Ledgeview Partners.