In the December update of Power Query, we included a variety of new features including support for Dynamics CRM Online. This is an exciting addition to the data sources that Power Query can connect to and will allow Dynamics CRM Online customers to load their data easily into Excel. In this post we’ll cover how to get the feed URL from Dynamics CRM Online, import it into Excel through Power Query and create some visualizations using Power View.
Microsoft Dynamics CRM Online OData Feed
To access Dynamics CRM Online data in Power Query, you’ll need to find the OData feed. First you’ll want to sign into your Dynamics CRM Online dashboard.
To get to the OData feed URL from the dashboard, navigate to the Developer Resources, under Microsoft Dynamics CRM > Settings > Customizations. Under Service Endpoints, you’ll see the Organization Data Service, Protocol: OData (REST). Clicking on the feed will show you the raw version in your browser. Copy the full URL for use in Power Query.
Accessing the feed in Power Query
Once you have copied the entire URL, go to the Power Query ribbon tab and choose the “From OData” data source (nested under the “From Other Sources” ribbon button). Paste in the full URL and click “OK”.
Use the Organizational Account credential and click “Sign in” to provide your Dynamics CRM Online credentials. After successfully authenticating, you can hit “Save” in the credentials dialog. You can always modify your credentials for this source through the “Data Source Settings” dialog in the Power Query ribbon.
After successfully authenticating and saving your credentials, you should see a list of tables in the Navigator side pane, each of them representing an entity from your Dynamics CRM Online instance. If you hit any issues, please check the URL of the feed and ensure the credentials are correct.
At this point you can navigate through the tables, hovering over each for a preview or choosing to select multiple tables. This experience is the same as with any other data source in Power Query, choose a table to load directly into Excel or choose “Edit Query” to shape the data into the result set you’re looking for.
Doing More with Power BI
This Dynamics CRM Online OData feed offers a lot of rich and detailed information about your CRM account and this Power Query update will allow you to access this data in Excel. After selecting an interesting table and doing some basic editing, you can leverage the other Power BI tools such as Power View to analyze and gain insights into your data.
Give it a try and connect to your own Dynamics CRM Online subscription through Power Query. Don’t hesitate to let us know your thoughts through the forums and through the Smile/Frown feature.
Helpful links: