cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

how to determine uncompressed table size in Desktop

I have a data source with a table that is ~15 [GB] in size (uncompressed).  Unfortunately, I need to pull the entire table.  My pbix file somehow has compressed the data down to 150 [MB].  I published my pbix file successfully to the service.  But, when I attempt to refresh the corresponding dataset in the service, I get the following error message:

 

The amount of uncompressed data on the gateway client has exceeded the limit of 10 GB for a single table. Please consider reducing the use of highly repetitive strings values through normalized keys, removing unused columns, or upgrading to Power BI Premium.

 

I understand that I need to limit the result set of my query to <= 10 [GB].  However, that means that, moving forward, I need to determine the size of my result set before I publish, as even though I can successfully publish the compressed size (150 [MB]), the subsequent refresh in the service will fail.  Is there some feature in Power BI Desktop that can show me the uncompressed data size per table?  Or, do I have to determine this myself, outside of the Desktop client?

4 REPLIES 4
Super User IV
Super User IV

Re: how to determine uncompressed table size in Desktop

I am not aware of anything in the desktop that will give you the uncompressed size. 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: how to determine uncompressed table size in Desktop

Hi there

It would appear that in your question is your answer.

You said that your table is 15GB, so that is what it needs to import before it can complete the compression. I would suggest splitting the table into 2 in the Power Query Editor and then appending them together




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Anonymous
Not applicable

Re: how to determine uncompressed table size in Desktop

I know that I need to do something to resolve the uncompressed data volume obstacle.  However, that is not my issue.  My issue is...  I need / want something in the Desktop client that warns me that the resulting uncompressed table is > 10 [GB].  I need / want this because, except in the very rare scenario that I exceed the 1 [GB] compressed limit, in general, I can publish a pbix file successfully for an uncompressed table that is > 10 [GB], only to be burned later on in the refresh of the dataset.  It would be nice to know that I'm going to have this problem in advance in the Desktop client.  Otherwise, I have to convert the M code to SQL code and run the SQL code in a SQL client to see if I'm exceeding the 10 [GB] uncompressed limit in the result set (table in the pbix file).  Since I work on data warehouse sources, my result sets are generally going to be > 10 [GB].  And, I don't want to purchase Premium to utilize the incremental refresh feature.

Super User IV
Super User IV

Re: how to determine uncompressed table size in Desktop

Hi there

From your original request you said that it was 150MB compressed?

I would always suggest removing all the columns and data that are not required using TSQL. Best practise is always to push back what you can to the data source.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors