Connect to a Power Query data source - Dynamics 365 Customer Insights (2024)

Power Query offers various connectors to ingest data, most of which Dynamics 365 Customer Insights supports. 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.

Power Query has data size and performance limitations. It makes copies of data in the Dataverse managed data lake in the CSV format so data synchronization takes longer as opposed to other data source connections.

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 a 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 tables that the systems identified in your selected data source appear in the left pane.

   

9. Transform your data. Select a table to edit or transform. To apply transformations, use the options in the Power Query window. Each transformation is listed under Applied steps. Power Query provides numerous prebuilt 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 more tables to your data source in the Edit queries dialog, go to Home and select Get data. Repeat steps 5-10 until you add all tables for this data source. If you have a database that includes multiple datasets, each dataset is its own table.

11. Select whether you want to refresh the data source manually or automatically. To refresh automatically, set the time frame.

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

    See Also

    Learn to combine multiple data sources (Power Query)Power Query Folder connector - Power QueryMerge queries (Power Query) - Microsoft SupportCreate a Data Model in Excel

    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.

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 are 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.

Best practices and troubleshooting

Because an on-premises data gateway is in organization's network, Microsoft can't check its health. The following recommendations can help address gateway timeouts when importing data to Customer Insights:

• Monitor and optimize on-premises data gateway performance and follow the on-premises data gateway sizing guide.

• Separate the ingestion and transformation dataflow. Separation of dataflows for ingestion and transformation is helpful when dealing with multiple queries of slower data sources in one dataflow or multiple dataflows querying the same data sources.

• Separate the entities into multiple dataflows.

• Choose the right connector and filter early.

• Make sure all on-premises data gateway nodes are healthy and configured at decent network latency between the nodes and data source for SQL instances.

• Use a scalable data gateway cluster if you expect heavy data requests.

• Ensure the data source is scaled out appropriately and the resource utilization on the source isn't abnormally high.

• Consider partitioning large tables into smaller tables.

• Consider hosting the data source and data gateway in the same geographical region.

  See Also

  Append queries (Power Query) - Microsoft Support

• Optimize the data source query and indexes. Properly indexed and partitioned data can be accessed more quickly and efficiently, leading to better query and dataflow performance.

Edit Power Query data sources

You must be the owner of the dataflow to edit it.

1. Go to Data > Data sources. Next to the data source you'd like to update, select Edit.

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

3. 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.

1. Go to Power Apps.

2. Select the Dataverse environment that maps to your Customer Insights - Data environment.

3. Go to Dataflows and select All Dataflows.

4. Search for the owner of the dataflow that you want to take ownership.

5. Select the vertical ellipsis (⋮) and select Change Owner.

6. 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. Remove the separate schedule. After a system refresh, the status changes to Completed.

Remove Power Query schedules

1. Go to Data > Data Sources.

2. Select the desired Power Query data source.

3. Select the vertical ellipsis (⋮) and select Edit refresh settings.

4. Select Refresh manually.

5. Select Save.

View Power Query run durations

1. Go to Data > Data Sources.

2. Select the desired Power Query data source.

3. 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.

1. Go to Data > Data Sources.

2. Select the desired Power Query data source, and then select Refresh.

Next steps

• Data unification overview