Creating/Replacing Marketing Lists in Dynamics 365 from Power BI - Part 5

Colin Maitland, 22 January 2021

In my first article of this series, I introduced a demonstration of a custom Canvas App, named ‘Marketing List App’, that is used to create new or replace existing static Marketing Lists in Dynamics 365 populated with a list of Supporters selected in a Power BI report.

In this article I will describe an approach for ensuring that a list of many thousands of Supporters selected in the Power BI report are made available to the Canvas App.

This approach resolves the limitations encountered so far, and described in my previous articles, when attempting to use the ‘PowerApps for Power BI’ visualisation in the report and corresponding ‘PowerBIIntegration’.Data component in the app or attempting to use the ‘OneDrive’ or ‘OneDrive for Business’ connector to connect to a Microsoft Excel file that has been exported from the Power BI report.

Here is an outline of the complete end-to-end process. The user …

1. uses the Slicers in the Power BI report to filter the Supporters

2. optionally also uses Report Level Filters in the Power BI report to filter the Supporters

3. exports the selected Supporters from the Power BI report to a Microsoft Excel file named ‘Supporters (Marketing List App).xlsx’

4. saves the Microsoft Excel file to specified Document location in Microsoft SharePoint

5. starts a process that imports the contents of the Microsoft Excel file into a Common Data Services (CDS) entity

6. uses the ‘Marketing List App’ to create new or replace existing Marketing Lists from the selected list of Supporters.

The design of the Power BI report guides the user through this process. For instance, this page of the report provides instructions for the user and corresponding navigation buttons.

The first four steps take one to three minutes to complete. The fifth step takes approximately one minute per 1,000 selected Supporters to complete. This is the longest step in the end-to-end process. The sixth step takes one to three minutes to complete.

In the remainder of this article will describe how the list of Supporters are imported from the Microsoft Excel file into the CDS entity for use by the ‘Marketing List App’.

The Marketing List App requires the following fields for each Supporter, the CustomerKey and the EntityTypeCode. The CustomerKey is a ‘contactid’ and the EntityTypeCode is 2 if the Supporter is an Individual (i.e. a Dynamics 365 Contact), and the CustomerKey is an ‘accountid’ and the EntityTypeCode is 1 if the Supporter is an Organisation (i.e. a Dynamics 365 Account).

The following image shows the columns for the custom CDS entity. The physical name of this entity is ‘mag_marketinglistapp’ and its display name is ‘Supporters (Marketing List App)’.

A Power Apps Data Flow named ‘Refresh Supporters (Marketing List App)’ and related Power Automate Flow named ‘Marketing List App (Populate Lookup Fields)’ are used to refresh the contents of the ‘Supporters (Marketing List App)’ CDS entity. In this article I will only describe the Power Apps Data Flow.

When importing the list of Supporters from the Microsoft Excel file into this entity the following columns are populated:

The ‘Refresh Supporters (Marketing List App)’ Data Flow has one Query and four Parameters. The Parameters are used to configure the name and location of the Microsoft Excel File. The Query named ‘Supporters (Marketing List App)’ imports the Supporters from Table1 in the Microsoft Excel file into the ‘Supporters (Marketing List App)’ CDS entity.

The Query has these eight steps:

1./2. The Source and Navigation Steps simply connect to Table1 in the Microsoft Excel file using the ‘Excel’ connector and a File Path or URL similar to ‘https://acme.SharePoint.com/Sites/MarketingListApp/Shared Documents/Supporters (Marketing List App).xlsx’. Once connected to the ‘Supporters (Marketing List Ap).xlsx’ Microsoft Excel file, select Table1.

3. The Choose Columns step chooses only the CustomerKey and EntityTypeCode columns. Any other columns that might exist in the Microsoft Excel file are not used by the ‘Marketing List App’ and so do not need to be imported.

4. The Added Index step adds a unique sequential Index column to every row. This Index is used by the next step.

5. The Added MarketingListAppGroup step then groups every row into multiples of 1,000 based on the Index as follows:

Rows 0 to 999 will be assigned to MarketingListAppGroup 1; rows 1,000 to 1,999 will be assigned to MarketingListAppGroup 2; . . .; and rows 148,000 to 149,999 will be assigned to MarketingListAppGroup 150.

Because a maximum of 150,000 rows can be exported from Power BI to Microsoft Excel, there would only ever be a maximum of 150 MarketingListAppGroup values when grouped into groups of 1,000.

A modification of this approach would be to make the sequential Index specific to Individuals versus Organisations and to group them into groups of 2,000.

The presence of the MarketingListAppGroup is very important for the Marketing List App being able to access all the rows in the CDS entity using a delegated query. The Marketing List App uses the MarketingListAppGroup to load the selected Supporters from the CDS entity into one of two collections, one for Individuals and one for Organisations, in batches of 1,000. This will be demonstrated in my next article.

6. The Remove Index step removes the Index column because this column is no longer needed.

7. The Change Column Type step changes the CustomerKey column to Text and the EntityTypeCode and MarketingListAppGroup columns to Whole Numbers.

8. The Rename Columns steps renamed CustomerKey to ‘mag_Name’, EntityTypeCode to ‘mag_EntityTypeCode’ and MarketingListGroup to ‘mag_MarketingListAppGroup’. This ensures that automatic mapping is effective at the Field Mapping step of the Data Flow configuration.

One very important aspect of the configuration of the Data Flow, is that it needs to remove any previously imported Supporters from the ‘Supporters (Marketing List App)’ entity that do not exist in the current list of Supporters imported from the ‘Supporters (Marketing List App)’ Microsoft Excel file. This is accomplished in the following manner:

1. Prior to configuring the Data Flow, add an Alternate Key based on the CustomerKey, i.e. the ‘mag_name’, field to the ‘Supporters (Marketing List App)’ CDS entity.

2. At the Load Settings and Field Mappings step of the Data Flow configuration select the ‘Delete rows that no longer exist in the query output’ option and ensure that the ‘mag_Name’ field is selected in the Alternate Key Fields section. Without the existence of this Alternate Key, the ‘Delete rows that no longer exist in the query output’ option cannot be used.

Finally, the Data Flow is configured to be run manually rather than on a schedule. When required, the user can navigate to the Data Flows page by clicking Data Flows in the Power BI report and select the Refresh option on the ‘More options’ menu of the Data Flow.

This approach resolves the limitations of the previously reviewed approaches. However, it does have these three limitations:

– Firstly, the Data Flow cannot set lookup fields, such as the ‘mag_accountid’ and ‘mag_contactid’ lookup fields. However, the automatically triggered ‘Marketing List App (Populate Lookup Fields)’ Power Automate Flow sets these fields as each ‘Supporters (Marketing List App)’ record is created by the Data Flow.

– Secondly, Power Apps Data Flows cannot be shared. Therefore, a separate copy of the Data Flow needs to be created for and owned by each User. In this implementation, this was not an issue, because there were only three Users.

– Thirdly, users cannot access the Data Flows area unless they have a Security Role such as System Administrator, System Customiser, Environment Admin or Environment Maker. A good option here is to use the Environment Admin Security Role after first optionally removing almost all its existing permissions, adding a few additional permissions, and then also optionally renaming it. Using a modified copy of the Environment Admin Security Role does not work. It must be the original. Use of this Security Role is a good option because its use for administering the Power Platform Environment is superseded by the System Administrator Security Role. The System Administrator Security Role is created once a database has been created for the Environment. The following table shows the permissions I used.

If the methods of resolving these limitations are not suitable for your implementation, then a custom user invoked application or service may be used to refresh a CDS entity.

In my next article, I will demonstrate how my ‘Marketing List App’ is able to access all the Supporters in the ‘Supporters (Marketing List App)’ CDS entity with no limits on the number that may be accessed.