Unanswered

Data Connector in Excel with refresh limit on small dataset

(1) Share

Report

Posted on by 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?

Categories:

Microsoft Dataverse with Power Apps

ErrorMessageConne...

I have the same question (0)

All responses (16)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @ChrisOJ

I’m afraid not, it is a hard limitation with the add-in. Suggest to lodge a Microsoft support ticket at aka.ms/ppac for more info about the limit and also submit a new idea at:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas to increase the limit.

Cheers

Was this reply helpful? Yes No
ChrisOJ 32 on at

Like (0)

Report

Hi Eric, thanks for your proposal. I am going to raise my concern there. My main question is: My dataset is much smaller than the 1000000 cells. How come this limitation hits me on about 7000 cells already?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @ChrisOJ,

Just another thought is to bring this into Power BI. You will be able to easily bring all this data in and create complex reports / dashboards.

Hope this helps!

Thanks,

Drew

Was this reply helpful? Yes No
ChrisOJ 32 on at

Like (0)

Report

Thanks for the hint on Power BI.
Actually I would prefer to have the data in Excel and to work with them by myself. How can you link dataverse data to your own devices?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

HI @ChrisOJ,

Can you explain in more detail what you mean by "How can you link dataverse data to your own devices?"?

Thanks,

Drew

Was this reply helpful? Yes No
ChrisOJ 32 on at

Like (0)

Report

Hi @dpoggemann ,
what I want to say with this:
1) Owning the data we would like to have these data as well on our own hard-drive, on our own computer, not only and exclusively in the cloud like dataverse.
2) We are a small company we have small datasets and I would like to be able to analyse the data in Excel. Here I know how to do all the graphs and correlations of the data.
3) I would not like to learn working with Power BI. Learning Powerapps is a big effort already. This is also a cost issue. We have just agreed to spend the money on premium powerapps with dataverse, now it comes with additional costs in order to see this data.
We have a very small dataset, it should be easy to export this to Excel with 1,000,000 cells. Why does it give me the error message by the moment I retrieve two tables to one Excel file?

Thanks for helping me.
Olaf

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @ChrisOJ,

Here is a good video that uses a combination of Power Apps (Canvas App) and Power Automate flow to generate a CSV file. https://youtu.be/imhxy3REgXc

I know this is a specific use case for a CSV file but I am expecting you could flow here to create your Excel file and store it in SharePoint. You can then have another flow that takes the file from SharePoint and stores on premises through the on premises gateway.

Hopefully this is helpful to get you down a path that will help.

Thanks,

Drew

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

That is strange indeed. There are hidden columns in Excel but it wouldn't make up that amount of cells! Note though that when using the Excel Add-in it doesn't take into account the filters applied in the view or in Power Apps portal so you'll have to re-apply some filters directly in Excel. Can that be the issue?
Also, any news from Microsoft support?

Was this reply helpful? Yes No
ChrisOJ 32 on at

Like (0)

Report

Hi @Eric ,
thank you for confirming my assessment. There are no filters applied by my side. Even with hidden columns there should be far less than 1 million cells.
I have sent a ticket to Microsoft just now and will let you know when I have an answer.

Was this reply helpful? Yes No
Verified answer

ChrisOJ 32 on at

Like (1)

Report

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.

Was this reply helpful? Yes No