Model Size

[Syndicate_Admin]

Hello World, Newbie User here.  I need help with a big dataset.  

Getting data from the DataVerse.

After removing the PowerBi plumbing and unwanted columns, the table has 101 columns and 508,628 rows.

When trying to load this big table into PowerBi, I get the error:

OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report. RequestId: TDS;e3727b21-0350-40fd-8508-19e1f35522cc;2 Time: 2021-03-26T12:55:48.2431825Z.

If I restrict the number of columns to be loaded into Power Bi to less than or equal to 20 columns, the table loads successfully.  If I request 21+ columns, the error returns.  It doesn’t seem to matter which 21+ columns are selected to load.

Is this normal? Is there a way to laod more than 21+ columns with so many rows? 

Can you help?

Thanks World!!

[Syndicate_Admin · Accepted solution]

The ODBC enpoint to Dataverse has an 80 Meg limit on the data it can return. 

You either need to chop up your query into smaller queries, or use another method to export the data. 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

[Syndicate_Admin]

Thanks Mister Fry!  I understand now.The file was indeed much larger than 80MB.

The problem lies with the documentation of the error message: "Return records size cannot exceed 83886080" led me to believe the error had something to do with the total number of records, not the total file size .

So much to learn, so little time.

Thank you again for your knowledge and experience.

[Syndicate_Admin]

We got the same error for Import.
Max size for the older OData protocol is higher as it didn't complain about the size.

[Syndicate_Admin]

DataSource.Error : Microsoft SQL : Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report.
RequestId: TDS;c3c748a8-fbbb-4e96-b53c-f4101e4f7fe4;2
Time: 2021-11-18T15:50:41.3471257Z
Détails :
DataSourceKind=CommonDataService
DataSourcePath=itesoft.crm4.dynamics.com
Message=Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report.
RequestId: TDS;c3c748a8-fbbb-4e96-b53c-f4101e4f7fe4;2
Time: 2021-11-18T15:50:41.3471257Z

[Syndicate_Admin]

@MisterFry 

EvenI had similar error in power query while merging two tables. I am creating a dataflow, I have one table in dataverse and other is excel file imported. Table in dataverse have a huge data, Excel have only 15 records. How can I shorten my query?

[Syndicate_Admin]

Hi @Krishna09 
There is a section in the documentation that I linked above called 'limitations', it makes reference to other options for interacting with dataverse data. I haven't done any of these things before, so I don't have definitive answers.