Cannot import more than 45k rows from Excel into Power BI Desktop

[zyildizk]

Hello everyone,

I have a problem with importing 112k rows (from excel). Each time Power BI gets only 45k rows (1 row column header + 44999 rows of data).

As I had seen before formatting the excel worksheet data as table (Home -> Format as Table), this solution works when implementing the reports. However, after publishing it I need the reports to be refreshed on daily basis while the data source (excel) is always in original format which is not fomatted as table. The excel file is being generated from a different system by Cognos so I cannot change this part much.

Have you been dealing with such problem before?

Many thanks in advance!

[v-easonf-msft]

Hi , @zyildizk 

It is not suggest to format the excel worksheet data as table  if the table to be refreshed   is always in original format every day.

What  we should consider is  how to create a query to get the complete data in the entire sheet.

This will make it unnecessary for us to format the excel worksheet data as table every time we refresh reports.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zyildizk]

@v-easonf-msft hello,

That is why I posted this question as I want to avoid formatting as table!

Thanks.

[Greg_Deckler]

Hmm, no and I just ran a test with the attached files, no problem what-so-ever. And fast! What version of Power BI Desktop and Excel are you running?

[zyildizk · Accepted solution]

My excel version is 2013, Power BI Version: 2.79.5768.721 64-bit (March 2020)

Yes you are right! When I also create my own excel with 112k it works. I copied the whole content of my original excel data source into a new excel worksheet that worked too!

I also tried one more thing which is interesting: I re-saved the original excel data source without changing anything in it and Power BI then imports all 112k rows.  So maybe as you say it is about the version of the original excel file which I cannot identify as it comes from Cognos, and when I open this file it opens with my excel version 2013.

Eventually how I solved it, is I converted the output of Cognos report setting from excel to csv. And with csv file Power BI takes in all rows.

Thank you for your response!

[Greg_Deckler]

Here is the Excel file I was testing with (attached)