Add data to an entity in Common Data Service by using Power Query

In this procedure, you'll create an entity in Common Data Service and fill that entity with data from an OData feed by using Power Query. You can use the same techniques to integrate data from these online and on-premises sources, among others:

  • SQL Server
  • Salesforce
  • IBM DB2
  • Access
  • Excel
  • Web APIs
  • OData feeds
  • Text files

You can also filter, transform, and combine data before you load it into a new or existing entity.

If you don't have a license for Power Apps, you can sign up for free.

Prerequisites

Before you start to follow this topic:

  • Switch to an environment in which you can create entities.
  • You must have a Power Apps per user plan or Power Apps per app plan.

Specify the source data

  1. Sign in to Power Apps.

  2. In the navigation pane, select Data to expand it, and then select Entities.

    Power Apps home page

  3. In the command menu, select Get data.

  4. In the list of data sources, select OData.

    Choose the OAuth connector

  5. Under Connection settings, type or paste this URL, and then select Next:
    https://services.odata.org/V4/Northwind/Northwind.svc/

  6. In the list of tables, select the Customers check box, and then select Transform data.

    Select the Customers table

  7. (optional) Modify the schema to suit your needs by choosing which columns to include, transforming the table in one or more ways, adding an index or conditional column, or making other changes.

  8. In the lower-right corner, select Next.

Specify the target entity

  1. Under Load settings, select Load to new entity.

    Specify the name of the new entity

    You can give the new entity a different name or display name, but leave the default values to follow this tutorial exactly.

  2. In the Unique primary name field list, select ContactName, and then select Next.

    You can specify a different primary-name field, map a different column in the source table to each field in the entity that you're creating, or both. You can also specify whether Text columns in your query output should be created as either Multiline Text or Single-Line Text in the Common Data Service. To follow this tutorial exactly, leave the default column mapping.

  3. Select Refresh manually for Power Query - Refresh Settings, and then select Create.

  4. Under Data (near the left edge), select Entities to show the list of entities in your database.

    The Customers entity that you created from an OData feed appears as a custom entity.

    List of standard and custom entities

Warning

If you use Power Query to add data to an existing entity, all data in that entity will be overwritten.

If you select Load to existing entity, you can specify an entity into which you add data from the Customers table. You could, for example, add the data to the Account entity with which the Common Data Service ships. Under Field mapping, you can further specify that data in the ContactName column from the Customers table should be added to the Name column in the Account entity.

Specify the name of the new entity

We're excited about this functionality and eager to hear your feedback. Please send us your suggestions and feedback about this feature!

If an error message about permissions appears, talk to your administrator.

Warning

There is a limit of 500,000 rows per run and per project that can be loaded using this feature.