Add data to a table in Microsoft Dataverse by using Power Query

In this procedure, you'll create a table in Dataverse and fill that table 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 table.

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

Prerequisites

Before you start to follow this article:

  • Switch to an environment in which you can create tables.
  • 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 Dataverse to expand it, and then select Tables.

    Tables area.

  3. In the command menu, select Data > 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 Next.

    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 table

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

    Load to new table.

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

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

    You can specify a different primary-name column, map a different column in the source table to each column in the table 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 Dataverse. To follow this tutorial exactly, leave the default column mapping.

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

  4. Under Dataverse (near the left edge), select Tables to show the list of tables in your database.

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

    List of standard and custom tables.

Warning

Existing data might be altered or deleted when loading data to a Dataverse table while having the Delete rows that no longer exist in the query output enabled or a primary key column defined.

If you select Load to existing table, you can specify a table into which you add data from the Customers table. You could, for example, add the data to the Account table with which the Dataverse ships. Under Column 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 table.

Specify the name of the new table.

If an error message about permissions appears, contact your administrator.