Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zyildizk
New Member

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

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!

 

 

1 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!

 

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

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.

 

 

@v-easonf-msft hello,

 

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

Thanks.

Greg_Deckler
Super User
Super User

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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!

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.