Power is like the year 2020, full of surprises. You must be wondering what did it do again! Well, it really surprised me the other day. When I was exporting data from Power BI, I've faced a very unexpected result, and this post is about that result. And of course, about the solution to that.
This is my data table. As you see, the table has 23,443 rows. On the report view, I have created a table visual by taking all the 23,443 rows and all the columns of this table. And there is no explicit filter on that visual like report level, page level, or visual level filter. After that, I have clicked on Export data option. That option creates a CSV file that you can save on your computer.
And this is the CSV file that Power BI has created.
In the beginning, everything seemed quite normal. But when I kept scrolling down, I have noticed that I have much less row than I was supposed to have. When I was creating this file I was hoping to find all the 23,443 rows on the visual. But 167 is not even close to 23,443. It seems like when we use Export data option, it might only take some random rows, and it's not top 167 or bottom 167. Power BI chose some rows to import completely randomly.
The solution to this problem is using Copy table option. After copying the table, you can directly paste it into Excel. If you don't need to copy the whole table, you can only copy the columns that you need and paste them. Copy table option is available at Data view at Power BI Desktop. And on Power Query, the same thing can be done by clicking on Copy Entire Table option.
For experimental purposes, I have used the Export data option with other tables and columns as well. It doesn't always disappoint you, sometimes it takes an entire table, sometimes not. That's why it's always better to check the result before taking the next step.
Comments