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

Colin Maitland, 30 November 2020

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 introduce a second possible approach that may be used for making the list of Supporters selected in the Power BI report available to the embedded Canvas App.

This second approach is to connect the Canvas App to a Microsoft Excel file that has been previously exported from Power BI.

However, there are several limitations with this approach related to the number of rows that can be exported from Power BI to Microsoft Excel, the maximum number of rows in a Microsoft Excel table and the maximum file size of a Microsoft Excel file that may be accessed by a Canvas App, that may be accessed by a Canvas App.

In Power BI, you can use the ‘Export data’ option on the ‘More options’ menu of a visualisation such as a table or a matrix to export the rows displayed by the visualisation to a Microsoft Excel or a CSV file. In Power BI Online, the maximum number of rows that may be exported to a Microsoft Excel file is 15,000. If exporting to a CSV file, the maximum number of rows that may be exported is 30,000.

For my ‘Marketing List App’ no more than 150,000 Supporters need to be added to a Dynamics 365 static Marketing List. Therefore, this limitation is not applicable.

For my ‘Marketing List App’ only the CustomerKey and EntityTypeCode columns need to be exported because these are the only columns used by the app. Other columns, such as Supporter Name can be optionally included as well regardless of whether they are used or not used by your Canvas App.

The Microsoft Excel file contains a table named ‘Table1’ that is populated with the rows that were exported from Power BI.

There are several connectors available for you to use in a Canvas App for connecting to a Microsoft Excel file. These include the Dropbox, Google Drive, OneDrive, OneDrive for Business and SharePoint connectors. There is also an ‘Excel Online (Business)’ connector. However, the ‘Excel Online (Business)’ connector is not for use by a Flow rather than by a Canvas App.

In my next article I will review use of the OneDrive for Business connector to connect the Canvas App to the exported Microsoft Excel file.

In a subsequent article, I will then review another approach that permits many thousands of Supporters to be made available to the Canvas App. More, than is permitted when using the approaches discussed so far. I will also publish an article that describes how the Canvas App and related components can be configured to ensure that static Marketing Lists in Dynamics 365 for many thousands of Supporters added as Marketing List Members can be created in only a few seconds.