Forum Discussion

Chris1300's avatar
Chris1300
Helper II
4 years ago
Solved

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 g...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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 

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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 

cherokee9's avatar
cherokee9
Frequent Visitor
1 year ago
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.
  • ouyay25's avatar
    ouyay25
    Frequent Visitor
    6 months ago

    right click on the list column -> To table