Microsoft unveiled Power BI for Office 365 and Power Query for Excel last summer, and both are still in preview mode. Power BI for Office 365 is a cloud-based solution that makes it easy to deploy a self-service business intelligence (BI) environment for sharing live Excel based reports and data queries, and it enables data discovery and information access from anywhere and from any device. Power Query for Excel is an Excel add-in that enhances the self-service BI experience in Excel by simplifying data discovery and access.
In December, Microsoft’s updates for Power BI and Power Query included support for Microsoft Dynamics CRM, which opens up yet another world of possibilities for your CRM data. Power Query enables you to quickly and easily load your CRM data into Excel, and after selecting the table you want and doing some basic editing, you can use Power BI tools such as Power View to analyze and gain insights into your data, as demonstrated below.
Power BI makes data mining simple with a search feature that works like an internet search engine. If you want to know what accounts are closing next month in New Jersey, for example, you just have to type that in: “All accounts closing next month in New Jersey.” Power Query enables you to access data from external sources, such as US government data.
To access Microsoft Dynamics CRM Online data in Power Query, you’ll need to find the OData feed URL. Copy that, go to the Power Query ribbon tab, and choose the “From OData” source (nested under the “From Other Sources” button). Paste the URL there. Authenticate and save your credentials, and you’re good to go.