Question – I am trying to "Enter Data" in Power BI 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.
Answer –
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 which will help you to understand – https://1drv.ms/x/s!Akd5y6ruJhvhuXwZMrH7laaLcVFj?e=ie2m5d
Note – I answered this question on Power Query forum – https://community.fabric.microsoft.com/t5/Power-Query/How-to-bypass-3000-cells-limit/m-p/2561720#M77953