Power Query, Copy Entire Table only copes 1000 rows

[LanaPow]

Power BI Desktop, office 365. 32bit, Win 10.

I have 25k rows table that i need to check in Excel 

using Copy Entire Table option only copies 1000 rows, unless I scroll down. Which is hardly "entire table", but rather, currently displayed part of the view..

I saw this community post here  and tried that, but pressing Ctrl+End only takes me to the last row that I scrolled to, i.e. 1000. 

If I scroll more, it takes me there. I have not found any other posts about it. 

But I really can't be scrolling down to 25r rows, each time it takes absolute ages.

a year ago when i worked with Power Query in excel (not power bi desktop), this was not a problem. 

please help

[az38 · Accepted solution]

Hi @LanaPow 

lets check

add index column in power query mode, then create a card visual and set MAX of this index as value

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

So, How can all the data(rows of 10k) from power query  editor (after some transformation) can be copied to excel ???

[az38 · Accepted solution]

Hi @LanaPow 

lets check

add index column in power query mode, then create a card visual and set MAX of this index as value

do not hesitate to give a kudo to useful posts and mark solutions as solution

[phaering]

I have this same question.  I have tried following along with our solution and I created the visual card.  When I export the data off the visual card I only get two cells of data and not the 75K rows and 40 columns of data I need to see in a worksheet in Excel.  Did I miss a step or what am I not doing that will allow me to export all the data from the table and not just the first 1K rows?  Thank you!

[LanaPow · Accepted solution]

Hi Az, thank you.   I did that. The Max of the index is the number of rows in the table. What next? 

[az38]

@LanaPow 

only 1000 rows is not an error.

there are two mode of data view:

power query (power editor) is the only PREVIEW of data. it helps you to understand what data modification  you need to do for your business logic achievement

in the normal mode (where you are seeing the correct max value) each of your rows (more then 1000) participatec in your calculation

dont worry, calculations are gonna be good, even if in power editor mode dont see all your data

do not hesitate to give a kudo to useful posts and mark solutions as solution

[LanaPow]

@az38   thank you! 🙂

[LanaPow]

Hi @az38 

ok i've explored around a bit, and i found that i can export the data from this visual into a file ))))

it's really cool how you answered, made me do some foot work but also helped ))

Thank you!