Use Upsert to insert or update a record

You can reduce the complexity involved with data integration scenarios by using the UpsertRequest message. When loading data into Microsoft Dynamics CRM from an external system, for example in a bulk data integration scenario, you may not know if a record already exists in CRM. In such cases you will not know if you should call an UpdateRequest or a CreateRequestoperation. This results in your querying for the record first to determine if it exists before performing the appropriate operation. You can now reduce this complexity and load data into CRM much more efficiently using the new UpsertRequest (Update or Insert) message. This topic discusses how to use UpsertRequest and the process that unfolds when an UpsertRequest is received.

For Microsoft Dynamics CRM Online organizations, this feature is available only if your organization has updated to Dynamics CRM Online 2015 Update 1. This feature is not available for Dynamics CRM (on-premises).

**This topic is pre-release documentation and is subject to change in future releases.

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.


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


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.


Paste the OData URL into URL field on OData Feed dialog


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


Click Sign in to connect to CRM instance.


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.


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


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.


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.