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

Colin Maitland, 16 December 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 a previous article I introduced 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 using one of the available connectors such as that for Dropbox, Google Drive, OneDrive, or OneDrive for Business.

In this article, I will provide a review of the OneDrive for Business connector.

OneDrive for Business Connector

If you want to save your exported Microsoft Excel file to a folder location in OneDrive or OneDrive for Business, there are two connectors available for connecting a Canvas App to the Microsoft Excel file. These are the ‘OneDrive’ and ‘OneDrive for Business’ connectors.

After adding the ‘OneDrive for Business’ connector to a Canvas App you will be required to search for and select the Microsoft Excel file to connect to. For my ‘Marketing List App’, this is the file named ‘Supporters (Marketing List App).xlsx’.

After selecting the Microsoft Excel file, you will then be required to select the table to connect to. For the Microsoft Excel file exported from Power BI, the table name is ‘Table1’. Once connected to this table, the name of the connection in the Canvas App is also ‘Table1’.

Here is an example of a formula in my ‘Marketing List App’ that counts all the Supporters, those Supporters that are Organisations and those Supporters that are Individuals in ‘Table1’ and assigns the results to corresponding Context Variables:

In my ‘Marketing List App’, these counts are displayed in a message at the top of the screen. Here you will see that the total number of Supporters counted is 500. However, this is well short of the count of 25,794 Supporters exported from Power BI to Microsoft Excel shown in the first image in this blog.

The limitation on how many rows in a Microsoft Excel table that may be accessed using the ‘OneDrive for Business’ connector is imposed by the ‘Data row limit for non-delegable queries’ under Advanced Settings in the Canvas App. The default value for this setting is 500 and the maximum value for this setting is 2,000.

After changing this setting to its maximum value of 2,000, the maximum number of rows in a Microsoft Excel table that may be accessed using the ‘OneDrive for Business’ connector is now 2,000.

Here you will see that the total number of Supporters counted is now 2,000. However, this is still well short of the count of 25,794 Supporters exported from Power BI to Microsoft Excel shown in the first image in this blog.

When using the ‘OneDrive’ and ‘OneDrive for Business’ connectors, there is a limitation with how many rows may be accessed by a Canvas App that is connected to a Microsoft Excel table; that limitation is based on the configuration of the ‘Data row limit for non-delegable queries’ setting. No other rows in the table above the number of rows defined by this setting can be accessed.

Therefore, this approach cannot be used if you want a Canvas App to access more than 2,000 from a Microsoft Excel file. For my ‘Marketing List App’ I need to be able to access up to 150,000 Supporters, which is the maximum number of rows that may be exported from Power BI to Microsoft Excel.

There is also another limitation related to the size of the Microsoft Excel file. When connecting to a Microsoft Excel file using the ‘OneDrive’ or ‘OneDrive for Business’ connector, the size of the file cannot be larger than 2 MB. Even, if the original file is below 2 MB in size, it’s size can be increased to be over 2 MB by the ‘OneDrive’ or ‘OneDrive for Business’ connector, because the connector adds an additional unique id column named ‘__PowerAppsId__’ to the Microsoft Excel table it is connected to.

A final issue with this approach, is if the Microsoft Excel file is deleted and then replaced, the connection between the Canvas App and the Microsoft Excel file will be broken. Even if the replacement file has the same file name, the dataset name assigned and used by the connection will no longer be valid and an error message will be displayed.

For these reasons, this approach does not provide a robust solution for transferring data from a Power BI report to a Canvas App.

In my next article, I will review an 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 my ‘Marketing List App’ Canvas App and related components can be 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.