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?

[EricRegnier]

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

[ChrisOJ]

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?

[dpoggemann]

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

[ChrisOJ]

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?

[dpoggemann]

HI @ChrisOJ,

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

Thanks,

Drew 

[ChrisOJ]

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

[dpoggemann]

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 

[EricRegnier]

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?

[ChrisOJ]

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.