Power BI for Office 365 and CRM 2015: Get CRM data to Excel with a click


Update 20th Feb 2016:

Connecting CRM 2016 to Power Query in Excel 2013 with updated Odata feed

Original Post:

What is Power BI?

Power BI is a cloud-based service that works together with Excel to provide a complete self-service analytic solution. With both Excel to author reports and Power BI for Office 365 to share them, you can give everyone in your organization a powerful new way to work with data.

Power BI Excel graphic

In Excel, you can now discover, combine, model, analyze, and visualize data like never before.

With Power BI for Office 365 you can easily setup an online gallery for users to share insights, collaborate and access reports created in Excel, from anywhere on any device while asking questions in natural language to get live interactive answers through data visualizations.

Self-Service BI in Excel

Self-service BI features in Excel 2013 make discovering and visualizing data easy. These four features – Power Query, Power Pivot, Power View, and Power Map – work seamlessly together.

With Power Query, you can search the entire cloud for data, both public and private. Using multiple data sources, you can filter, shape, merge, and append that data using Power Query… without bringing any of it into Excel. You get a view into the data, shape it how you want, and can then send it directly to the Data Model in Power Pivot. And even then, you can create new calculations and fields in Power Pivot to get just the dataset you want.

Power View uses the Data Model to bring that data to life. Create reports, then let others interact and drill into them, changing visualizations on the fly as they select report elements.

Connecting CRM 2015 to Power BI Excel

Power Query for Excel uses OData feeds to get the data from CRM using OData Organizational Data Services. Using this one can get all the data from CRM into Excel and use the power of Excel for analyzing data in depth.

To start, one need to have Microsoft Power Query for Excel add-on installed. This can be download at below link.

http://www.microsoft.com/en-us/download/details.aspx?id=39379

PBI1

Navigate to your CRM organization and copy the Organizational Data Service URL from Setting > Customizations >Developer Resources.

PBI2

Open Excel and go to POWER QUERY tab and select From Other Sources button in Get External Data section. Here we have multiple data sources options to get data from to Excel like SharePoint, Azure, Web Search, Active Directory, Exchange, OData, etc.

For connecting to CRM, we will use From Odata Feed.

PBI3

Paste the OData URL into URL field on OData Feed dialog

PBI4

Go to Organizational account tab and select the OData URL to use.

PBI5

Click Sign in to connect to CRM instance.

PBI6

On successful sign in, click on save. This will open a query window where one can select the CRM table which you want to query data and apply different filters.

PBI7

The filtered query can be saved for future use and the data returned will be displayed in Excel worksheet.

PBI8

Once the data is in excel, your open to use all Excel features to work with the data outside CRM and build the report as you wish with few clicks.

PBI9

Other sources that Power BI supports for getting data into Excel with few configurations are Web (search), File (Excel, CSV, XML, Text, Folder), Database (SQL Server, Access, SQL Server Analysis Services, Oracle, IBM DB2, MySql, PostgreSQL, Sybase, Teradata), Azure, SharePoint, OData Feed, Hadoop File, Active Directory, Microsoft Exchange, Facebook, SAP BusinessObject BI Universe, Salesforce Objects, Salesforce Reports, ODBC.

Advertisements

6 thoughts on “Power BI for Office 365 and CRM 2015: Get CRM data to Excel with a click

  1. Graham Hill January 17, 2015 / 1:25 pm

    Good blog to follow to start playing with CRM data.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s