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

Colin Maitland, 16 February 2021

In my first article of this series, I introduced a demonstration of a custom Power BI embedded 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.

The two most technically challenging aspects in implementing this functionally with respect to the large number of selected Supporters are firstly making all the Supporters available to the Marketing List App and secondly creating new or replacing existing Marketing Lists in a timely manner. Any number of Supporters from 1 to 150,000 may need to be processed.

The first technical challenge is resolved by the user first exporting the list of selected Supporters from Power BI to a Microsoft Excel file saved in SharePoint, and then manually starting a Power Apps Data Flow that imports the Supporters from the Microsoft Excel file into a custom entity. My previous article describes the configuration of this Data Flow named ‘Refresh Supporters (Marketing List App)’. This Data Flow takes approximately one minute per 1,000 Supporters to complete. This is the longest step in the end-to-end process. Other steps only require from a few seconds to a couple of minutes to complete.

In order to run this data flow, the Users

In this article, I will describe how the Marketing List App can access the many thousands of Supporters that have been imported by the Data Flow into the entity named ‘Supporters (Marketing List App)’.

In the following formulas, this entity is referred to as ‘[@Marketing List Apps]’.

My ‘Marketing List App’ uses this formula to import the CustomerKey values from the ‘mag_name’ field for any Supporters in the ‘Supporters (Marketing List App)’ entity that are Contacts into a collection named ‘collectionIndividuals’.

My ‘Marketing List App’ uses this formula to import the CustomerKey values from the ‘mag_name’ field for any Supporters in the ‘Supporters (Marketing List App)’ entity that are Accounts into a collection named ‘collectionOrganisations’.

These formulas are associated with the Refresh button in the ‘Marketing List App’. They both use delegation to retrieve the list of Supporters in groups of up to 1,000 at a time based on the value of ‘MarketingListAppGroup’ field in the ‘Supporters (Marketing List App)’ entity. Without the use of delegation, the Marketing List App would only be able to access a maximum of 2,000 Supporters.

The ‘Refresh Supporters (Marketing List App)’ includes a step that sets the value of the ‘MarketingListAppGroup’ field for each Supporter. In this implementation, the Supporters are grouped into groups of 1,000. However, they could be grouped into any group size up to 2,000.

The Filter() clause of the formula works in conjunction with the Sequence() and ForAll() clauses. The ForAll() clause is invoked once for each value in the sequence. The sequence starts at 1 and finishes at 150 in increments of 1.

This sequence accommodates the fact that there will not be any more than 150,000 Supporters in the entity. This because there will not be any more than 150,000 Supporters in the Microsoft Excel file that was exported from Power BI and from which the entity was refreshed by the Data Flow. Using a group size of 1,000, the MarketingListAppGroup values for each Supporter in the entity therefore will only ever fall withing the range 1 to 150. The Sequence() cannot start at 0 and so care has been taken to ensure that the MarketingListAppGroup values generated by the Data Flow start at 1 rather than 0.

The ‘EntityTypeCode = 2’ and ‘EntityTypeCode = 1’ parts of the Filter() clause, selects only those Supporters based on whether they are Individuals or Organisations. This aligns with the fact that Individuals and Organisations are loaded into separate Collections by the Power App.

The ‘MarketingListAppGroup = Value’ part of the Filter() clause, selects only those Supporters that are in the ‘MarketingListAppGroup’ with the same value as that of the current value of the Sequence().

The ShowColumns() clause of the formula ensures that only the ‘mag_name’ field is retrieved from the entity. This field contains the CustomerKey for each Supporter. This clause optimises the performance of the formula. My Marketing List App does not need access to any other fields. Alternately, the formula could have instead selected the ‘mag_accountid’ and ‘mag_contactid’ columns from the entity.

The RenameColumns() clause of the formula renames the retrieved ‘mag_name’ column to either ‘contactid’ or ‘accountid’ based on whether the Supporter is an Individual or an Organisation because the CustomerKey is actually either an ‘accountid’ or a ‘accountid’.

The Collect() clause of the formula adds the Supporters retrieved by the current iteration of Sequence() to the corresponding collection. Depending on the total number of Supporters to be retrieved, not all iterations Sequence() invoked by the ForAll() clause will result in any additional Supporters being retrieved and added to the corresponding collection. However, inserting additional logic within the ForAll() loop to count the number of Supporters retrieved by the current iteration is not possible. However, both formulas iterate through the entire sequence in less than 30 seconds.

Finally, once all the Supporters have been retrieved, a count of the total number of Supporters, those that are Individuals, and those that are Organisations are calculated as follows:

In my ‘Marketing List App’, these counts are displayed to the User and are used to determine whether certain controls in the ‘Marketing List App’ are enabled or disabled.

However, because of the method used by my ‘Marketing List App’ for creating new or replacing existing Marketing Lists for the selected Supporters, the ‘Marketing List App’ does not need to retrieve the Supporters from the ‘Supporters (Marketing List App)’ entity. For this reason, the following optimised formula can be used to count the Supporters.

The reason, the Supporters are added to a collection prior to then counting them is because the UpdateContext() function cannot be used inside the ForAll() function. Otherwise, we could just progressively increment a counter rather than progressively add records to a collection using a formula like the following:

In my next article I will describe how my ‘Marketing List App’ and related components are configured to ensure that static Marketing Lists in Dynamics 365 with many thousands of Supporters added to them as Marketing List Members can be created in only a few seconds.