Edit

Connect to a Power Query data source

  • Article
  • 6 minutes to read

Power Query offers a broad set of connectors to ingest data. Most of these connectors are supported by Dynamics 365 Customer Insights. Adding data sources based on Power Query connectors generally follows the steps outlined in this section. However, depending on the connector you use, different information is required. To learn more, see the documentation about individual connectors in the Power Query connector reference.

To securely connect data in a private network, Power Query supports the use of virtual network data gateways (preview).

Create a new data source

  1. Go to Data > Data sources.

  2. Select Add data source.

  3. Select Microsoft Power Query.

  4. Provide a Name and an optional Description for the data source, and select Next.

  5. Choose one of the available connectors. In this example, we select the Text/CSV connector.

  6. Enter the required details in the Connection settings for the selected connector and select Next to see a preview of the data.

  7. Select Transform data.

  8. Review and refine your data in the Power Query - Edit queries page. The entities that the systems identified in your selected data source appear in the left pane.

    Edit queries dialog

  9. Transform your data. Select an entity to edit or transform. Use the options in the Power Query window to apply transformations. Each transformation is listed under Applied steps. Power Query provides numerous pre-built transformation options.

    Important

    We recommend you use the following transformations:

    • If you're ingesting data from a CSV file, the first row often contains headers. Go to Transform and select Use first row as headers.
    • Ensure the data type is set appropriately and matches the data. For example, for date fields, select a date type.

  10. To add additional entities to your data source in the Edit queries dialog, go to Home and select Get data. Repeat steps 5-10 until you have added all entities for this data source. If you have a database that includes multiple datasets, each dataset is its own entity.

  11. Select Save. The Data sources page opens showing the new data source in Refreshing status.

    Tip

    There are statuses for tasks and processes. Most processes depend on other upstream processes, such as data sources and data profiling refreshes.

    Select the status to open the Progress details pane and view the progress of the tasks. To cancel the job, select Cancel job at the bottom of the pane.

    Under each task, you can select See details for more progress information, such as processing time, the last processing date, and any applicable errors and warnings associated with the task or process. Select the View system status at the bottom of the panel to see other processes in the system.

Loading data can take time. After a successful refresh, the ingested data can be reviewed from the Entities page.

Caution

  • A data source based on Power Query creates a dataflow in Dataverse. Don't change the name of a dataflow in the Power Platform admin center that is used in Customer Insights. Renaming a dataflow causes issues with the references between the Customer Insights data source and the Dataverse dataflow.
  • Concurrent evaluations for Power Query data sources in Customer Insights have the same refresh limits like Dataflows in PowerBI.com. If a data refresh fails because it reached the evaluation limit, we recommend you adjust the refresh schedule for each dataflow to ensure the data sources aren't processed at the same time.

Available Power Query data sources

See the Power Query connector reference for a list of connectors that you can use to import data to Customer Insights.

Connectors with a checkmark in the Customer Insights (Dataflows) column are available to create new data sources based on Power Query. Review the documentation of a specific connector to learn more about its prerequisites, query limitations, and other details.

Add data from on-premises data sources

Ingesting data from on-premises data sources is supported based on Microsoft Power Platform dataflows (PPDFs). You can enable dataflows in Customer Insights by providing the Microsoft Dataverse environment URL when setting up the environment.

Data sources that are created after associating a Dataverse environment with Customer Insights use Power Platform dataflows by default. Dataflows support on-premises connectivity using the data gateway. You can remove and recreate data sources that existed before a Dataverse environment was associated using on-premises data gateways.

Data gateways from an existing Power BI or Power Apps environment will be visible and you can reuse them in Customer Insights if the data gateway and the Customer Insights environment are in the same Azure Region. The data sources page shows links to go to the Microsoft Power Platform environment where you can view and configure on-premises data gateways.

Important

Make sure your gateways are updated to latest version. You can install an update and reconfigure a gateway from a prompt shown on the gateway screen directly or download the latest version. If you don't use the latest gateway version, the dataflow refresh fails with error messages like The keyword isn't supported: configuration properties. Parameter name: keyword.

Errors with on-premises data gateways in Customer Insights are often caused by configuration issues. For more information about troubleshooting data gateways, see Troubleshoot the on-premises data gateway.

Edit Power Query data sources

Note

It might not be possible to make changes to data sources that are currently being used in one of the app's processes (segmentation or data unification for example).

In the Settings page, you can track the progress of each of the active processes. When a process completes, you can return to the Data Sources page and make your changes.

  1. Go to Data > Data sources.

  2. Next to the data source you'd like to update, select Edit.

  3. Apply your changes and transformations in the Power Query - Edit queries dialog as described in the Create a new data source section.

  4. Select Save to apply your changes and return to the Data sources page.

    Tip

    There are statuses for tasks and processes. Most processes depend on other upstream processes, such as data sources and data profiling refreshes.

    Select the status to open the Progress details pane and view the progress of the tasks. To cancel the job, select Cancel job at the bottom of the pane.

    Under each task, you can select See details for more progress information, such as processing time, the last processing date, and any applicable errors and warnings associated with the task or process. Select the View system status at the bottom of the panel to see other processes in the system.

Common reasons for ingestion errors or corrupt data

Data type does not match data

The most common data type mismatch occurs when a date field is not set to the correct date format.

The data can be fixed at the source and re-ingested. Or fix the transformation within Customer Insights. To fix the transformation:

  1. Go to Data > Data sources.

  2. Next to the data source with the corrupted data, select Edit.

  3. Select Next.

  4. Select each of the queries and look for transformations applied inside "Applied Steps" that are incorrect, or date columns that have not been transformed with a date format.

    Power Query - Edit showing incorrect date format

  5. Change the data type to correctly match the data.

  6. Select Save. That data source is refreshed.

Tip

For troubleshooting information, go to Microsoft Dynamics 365 Customer Insights troubleshooting.