Connect to a Power Query data source
Power Query offers a broad set of connectors to ingest data, most of which are supported by Dynamics 365 Customer Insights. In the Power Query connector reference, connectors with a checkmark in the Customer Insights (Dataflows) column you can use to import data to Customer Insights. Review the documentation of a specific connector to learn more about its prerequisites, query limitations, and other details.
To securely connect data in a private network, Power Query supports the use of virtual network data gateways (preview).
Create a new data source
Go to Data > Data sources.
Select Add a data source.
Select Microsoft Power Query.
Provide a Name and an optional Description for the data source, and select Next.
Choose one of the available connectors. In this example, we select the Text/CSV connector.
Enter the required details in the Connection settings for the selected connector and select Next to see a preview of the data.
Select Transform data.
Review and refine your data in the Power Query - Edit queries page. The tables that the systems identified in your selected data source appear in the left pane.
Transform your data. Select a table 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.
To add additional tables 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 tables for this data source. If you have a database that includes multiple datasets, each dataset is its own table.
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 Data > Tables 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.
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.
Go to Data > Data sources. Next to the data source you'd like to update, select Edit.
Apply your changes and transformations in the Power Query - Edit queries dialog as described in the Create a new data source section.
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.
Feedback
Submit and view feedback for