Connect to a Power Query data source
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.
Create a new data source
Go to Data > Data sources.
Select Add 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.
The Power Query - Edit queries dialog lets you review and refine the data. The entities that the systems identified in your selected data source appear in the left pane.
You can also 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.
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. For example, for date fields, select a date type.
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.
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.
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. 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