Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Chris1300
Helper II
Helper II

How to bypass 3000 cells limit?

I am trying to "enter data" in powerBI that is a large table. I am doing this to avoid a connection and avoid refreshing data. I can copy the data from a csv file but when i paste into enter data i get an error with 3000 cell limit.

 

I then came across this tip to bypass: https://www.reddit.com/r/PowerBI/comments/mw9qji/bypassing_power_queries_enter_data_3000_row_limit/

 

But I am unable to follow the instructions and its not very clear. Can someone enlighten me on if this would actually bypass the 3000 cell limit? If so, can you provide better instructions?

 

Thanks!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

1. Import your csv into PQ

2. Insert a new step in this table

= Text.From( Binary.Compress( Binary.FromText( Text.From( Json.FromValue( Source ) ), BinaryEncoding.Base64 ), Compression.GZip ) )

3. Insert a new blank Query by right clicking the left pane

Put following formula

= Json.Document( Text.FromBinary( Binary.Decompress( Binary.FromText( "MASSIVE_WALL_OF_TEXT_GOES_HERE" ), Compression.GZip ) ) )

4. Copy the output of step2 and paste  into MASSIVE_WALL_OF_TEXT_GOES_HERE in above query

5. Click To Table under Transform

6. Click double edged arrow and extract all fields

I have prepared one sample on the basis of this and uploaded whch will help you to understand to https://1drv.ms/x/s!Akd5y6ruJhvhuXwZMrH7laaLcVFj?e=ie2m5d 

View solution in original post

3 REPLIES 3
mandysachswc
Frequent Visitor

I know this is an old post but for me everything is grayed out and I do not have the option to follow steps 5+

Vijay_A_Verma
Super User
Super User

1. Import your csv into PQ

2. Insert a new step in this table

= Text.From( Binary.Compress( Binary.FromText( Text.From( Json.FromValue( Source ) ), BinaryEncoding.Base64 ), Compression.GZip ) )

3. Insert a new blank Query by right clicking the left pane

Put following formula

= Json.Document( Text.FromBinary( Binary.Decompress( Binary.FromText( "MASSIVE_WALL_OF_TEXT_GOES_HERE" ), Compression.GZip ) ) )

4. Copy the output of step2 and paste  into MASSIVE_WALL_OF_TEXT_GOES_HERE in above query

5. Click To Table under Transform

6. Click double edged arrow and extract all fields

I have prepared one sample on the basis of this and uploaded whch will help you to understand to https://1drv.ms/x/s!Akd5y6ruJhvhuXwZMrH7laaLcVFj?e=ie2m5d 

I am very new to Power M. I've tried to follow these steps and was successful in doing steps 1-4. For step 5, I included your code as Source in Applied Steps with my step 2 massive wall between " copy/paste". I got the result as a List (1 column and it has "Records" that I can click to show the result in a separate view). Under Transform tab everything is greyed out though, and I can't convert the data to table.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors