Connect to a Power Query data source
Power Query offers a variety 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 - Data. 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 whether you want to refresh the data source manually or automatically. To refresh automatically, set the time frame.
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 - Data. Renaming a dataflow causes issues with the references between the data source and the Dataverse dataflow.
- Concurrent evaluations for Power Query data sources in Customer Insights - Data 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 - Data 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 - Data 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
Update your gateways to the 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.
Make sure that the gateway VM is sized appropriately. Power BI recommends that you start with a machine with at least 8 CPU cores, 8 GB of RAM, and multiple Gigabit network adapters. You can then measure a typical gateway workload by logging CPU and memory system counters.
Errors with on-premises data gateways 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
You must be the owner of the dataflow to edit it.
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.
Loading data can take time. After a successful refresh, review the ingested data from the Data > Tables page.
Transfer Power Query data source ownership
You can transfer the data source ownership to other people in your organization. For example, if the owner leaves the organization or if changes are required for collaboration purposes.
Transfer the ownership
The user performing this action must have a Dataverse Administrator role.
Go to Power Apps.
Select the Dataverse environment that maps to your Customer Insights - Data environment.
Go to Dataflows and select All Dataflows.
Search for the owner of the dataflow that you want to take ownership.
Select the vertical ellipsis (⋮) and select Change Owner.
Enter the name of the new owner and select Change Owner.
Update Power Query schedules to system refresh schedule
Customer Insights - Data is aligning Power Query separate refresh schedules with the system refresh schedule. To ensure that Customer Insights - Data reflects current data, remove your Power Query refresh schedules so that these data sources refresh as part of the system refresh. If your Power Query data source shows Completed with warnings on the Data sources page, your data source contains a separate refresh schedule. After successfully removing the separate schedule and a system refresh, the status changes to Completed.
Important
The data source refresh time is added to the total time for a system refresh. We recommend you view your Power Query run durations and then change the system refresh schedule if needed. For example, a Power Query source might take an average of 30 minutes to refresh. Therefore, we recommended you update the system refresh schedule to start 30 minutes earlier to recieve results at a simaliar time.
Remove Power Query schedules
Go to Data > Data Sources.
Select the desired Power Query data source.
Select the vertical ellipsis (⋮) and select Edit refresh settings.
Select Refresh manually.
Select Save.
View Power Query run durations
Go to Data > Data Sources.
Select the desired Power Query data source.
Select Status.
Refresh Power Query data sources on demand
Only the owner of a Power Query data source can refresh the data source on demand. If you aren't the owner of the data source, find the data source owner under Managed by others on the Data Sources page.
Go to Data > Data Sources.
Select the desired Power Query data source, and then select Refresh.