Data Connector in Excel with refresh limit on small dataset

[ChrisOJ]

There are 7 tables in my Dataverse database, with about 1000 custom cells (=rows * custom-columns) each.

Except two of these tables, all of them are connected with a Look-up field. I want to bring only the custom-columns to my Excel-sheet.

Each of these tables can be refreshed & published in Excel with the "Powerapps Add-in Data Connector".

When I try to store 2 or more tables in one Excel-Workbook, then the Data Connector gives me an error message "The data retrieved has been limited to the cell refresh limit of 1000000 cells. Apply additional filters as needed."

All my tables together have roughly 7000 cells, and I am surprised about this error message. Is there any way to get all the 7 table's data into one Excel-sheet in order to build custom reports on them that will refresh over time automatically?

Is there another way to get the data into Excel with a link that can be updated?

[ChrisOJ · Accepted solution]

Researching a solution with the support team of Microsoft I came to this conclusion:

Connect Dataverse tables to Excel

1) Get the Service Root URL

Go to the Power Platform Admin Center:

https://admin.powerplatform.microsoft.com/environments

Select the Environment that contains the tables you want to connect to and click "Open Environment" in the top.

The page that opens now likely looks like "Active Analysis Jobs". Here go to the cogwheel on the right top corner and click it. From the dropdown select "Advanced settings".

A page opens for "Dynamics 365 settings". Click onto the small drop-down arrow beside "Settings", go to the second column from the left called "Customization" and select the first entry "Customizations".

In the "Customization" page select "Developer Resources". The subtitle of "Developer Resources" is:

"View information or download files that help you develop applications and extensions for Microsoft Dynamics 365"

Under the headline "Connect your apps to this instance of Dynamics 365" go to "Instance web API" and find the "Service Root URL". Copy this "Service root URL" and paste it into a new blank Excel file.

2) Link Excel to Dataverse using the Service Root URL

In the blank Excel file go to "Data", "Get Data", "From Other Sources", "From OData Feed".

A small menu opens with the label "OData feed". Select "Basic" and paste the Service Root URL to the field.

In the new menu wait until it is fully loaded. Then open the root folder and find a lot of tables available in your Environment. Activate "Select multiple items" in case you want to import more than one table. Select the tables you are interested in and click "Load to...". Then you can select "table" and to save the data in a new worksheet.

Now you have a query in Excel that is linked to a table on Dataverse. You can refresh this table anytime and receive real-time information without a limit on 1,000,000 cells.

Thanks for all the contributers in this post and to the Microsoft Support Team.

[simes]

I get the same error - even when increasing the cell refresh limit (under Options) and when I know the cell count is well below that limit.

Did MS admit to any bug with the Microsoft PowerApps Office Add-in?  I find you just can't add a second table however small and have it load any data.

The workaround they gave you is fine if you only want to retrieve data but if you hoped to be able to create/update/insert/delete more than one entity/table from the same spreadsheet you are stuffed.  The ability to add multiple tables with the add-in's Design feature appears totally redundant and we need a bird's nest of related spreadsheets (in my case with quite a bit of duplication) to do the same job...

[ChrisOJ]

Hi simes,

the Microsoft support team only admitted that they reproduce this error on their side, and it is currently not possible to have more than 1 linked table in one Excel-Workbook.

The workaround above is only for retrieving data, this is correct.

Another solution I thought about was to save the data in SharePoint-lists and then link them to MS Access. But this is a bigger mission in itself and takes away a lot of advantages of Dataverse.

Then I also try to get better in creating screens in my PowerApp, that allow the bulk-editing of data inside my app.

If you have a solution please let us know.

[simes]

Hi Chris,

I ended up using PowerQuery like you.  That has the well known issue with D365/DataVerse/CDS of it becoming painfully slow as soon as one starts doing more than a few joins in PowerQuery (to resolve Lookups etc. and requiring yet more workarounds...)

I'll raise a new bug with MS when I get some time.  There's no point being able to add multiple tables to the Microsoft PowerApps Office Add-in if it all stops working when you do.

[rmsantos]

Unfortunately, this bug still exists. Did you find a work-around for this? My users need to retrieve, create, edit, and delete records in multiple tables in one worksheet. 

[ChrisOJ]

Hi Santos, I do not know a solution to edit multiple tables in Excel. The only work around is to create an app that can access all these tables and edit them there. This is also very frustrating for me.

[simes]

More of a workaround than a solution?  If you wanted to create/update/delete rows in more than one table PowerQuery doesn't help and you still need multiple spreadsheets, one per table.  If you just want to retrieve you can quickly run into performance issues with joins between multiple tables in PowerQuery and how to avoid those problems isn't always at all obvious.