cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Why is Power BI SO SLOW?!?

I'm currently working with some Excel file, all the Excel files combined are 728kb, NOT EVEN 1MB, but Power BI is taking forever when I refresh my data or try to merge tables together.

Does someone know why this would be the case? I applied a lot of steps to transform my data the way I like it, but I hope that it is not the reason why Power BI is struggling with a 700kb file?!?!?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Why is Power BI SO SLOW?!?

I saw your M code. 

 

I don't see anything earth-shatterningly wrong. What is happening though is you have roughly 70 steps going through 100,000 rows as you indicated. You have about a dozen REPLACE steps. Those steps go through all 100,000 rows every single time. I think that is probably the biggest single issue. You are also doing a lot of split columns, which is also time consuming on a flat file of that size.

 

The Replace Steps could mostly be replaced by merges to a DIM table and expanding what you need, but at this point I'dhave to have the file to really dig in. I know for a 100% fact this can be optimized, but I don't want to over promise, working with flat files in the 100,000+ record range can be slow if you are doing a lot of transformations.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

12 REPLIES 12
Highlighted
Super User VI
Super User VI

Re: Why is Power BI SO SLOW?!?

We would need to see the transformations. If you do a lot of merges, create structured (nested) columns, etc., you can get Power Query into a knot.

 

If you post your M code we might be able to see what is wrong, but may need your source and PBIX file if you can remove any confidential data.

 

I can get Power Query to go south with just a few thousand rows of data inefficiently transformed.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper V
Helper V

Re: Why is Power BI SO SLOW?!?

Unfortunalety I can't share my PBIX file due to confidential data, but here are some screens of my applied steps on my fact table:

Gjakova_0-1595887478393.pngGjakova_1-1595887505646.pngGjakova_2-1595887535494.png

Because I unpivoted my fact table, it went from 20+ columns and 1000 rows to 7 columns and 100.000+ rows, since then I didn't do any shocking transformations (I think), but above you can have a look on all the steps I have taken so far...

Kind regards!

Highlighted
Super User VI
Super User VI

Re: Why is Power BI SO SLOW?!?

No. I need the M code.

In the advanced editor (home ribbon), CTRL-A, then paste it here in the code box. Use the </> icon. Sort of the reverse of the steps listed below that let you use M code posted here.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User VI
Super User VI

Re: Why is Power BI SO SLOW?!?

I saw your M code. 

 

I don't see anything earth-shatterningly wrong. What is happening though is you have roughly 70 steps going through 100,000 rows as you indicated. You have about a dozen REPLACE steps. Those steps go through all 100,000 rows every single time. I think that is probably the biggest single issue. You are also doing a lot of split columns, which is also time consuming on a flat file of that size.

 

The Replace Steps could mostly be replaced by merges to a DIM table and expanding what you need, but at this point I'dhave to have the file to really dig in. I know for a 100% fact this can be optimized, but I don't want to over promise, working with flat files in the 100,000+ record range can be slow if you are doing a lot of transformations.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Helper V
Helper V

Re: Why is Power BI SO SLOW?!?

"Those steps go through all 100,000 rows every single time." @edhans 

 

Hmm... I did not know that. Thanks for the tip! So what would you advice to a beginner like me when working with such datasets? Because when I duplicate the fact table and remove other columns to create a dimension table, all the previous steps are still registered. Is there a better solution to solve my issue in Power BI, so that I can avoid doing all those steps (or at least don't mess up the performance of Power BI.

Highlighted
Helper V
Helper V

Re: Why is Power BI SO SLOW?!?

Instead of these many steps inside Power BI, try to create a View in the source and use that View as the source with all necessary actions such as Rename, Split, etc..

Highlighted
Helper V
Helper V

Re: Why is Power BI SO SLOW?!?

@amkumar5 what do you mean exactly with creating a "view" in the source? I am using Excel and csv files for this. So I can only transform it in Power BI itself, or do you mean something else? Thanks in advance!

Highlighted
Super User VI
Super User VI

Re: Why is Power BI SO SLOW?!?


@Gjakova wrote:

"Those steps go through all 100,000 rows every single time." @edhans 

 

Hmm... I did not know that. Thanks for the tip! So what would you advice to a beginner like me when working with such datasets? Because when I duplicate the fact table and remove other columns to create a dimension table, all the previous steps are still registered. Is there a better solution to solve my issue in Power BI, so that I can avoid doing all those steps (or at least don't mess up the performance of Power BI.


I'm not clear on what you mean by the steps are still registered.

 

It really comes down to optimizing the code so steps are repeated as little as possible. I would also like to see how your FACT table is being split off into DIM tables. If you are doing 70- transformations then creating 3 DIM tables, those 3 DIM tables force the full 70 transformations to run each time. Could there be a way to split off the relevant data with Refrences in Power Query first, then do only what you need for each DIM table, then finalize your FACT table with the necessary steps.

 

But we are talking a lot of theory here. It is very hard to help without seeing the file. I fully understand your confidentiality issues. I just want you to know why I am not providing a bullet point list of things to do. I don't have enough info to do that.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper V
Helper V

Re: Why is Power BI SO SLOW?!?

I understand your point. I would've loved to share it, but I'm not allowed to. But what you've just mentioned already make a lot of sense. I did not know that all those applied steps would weight so heavy on Power BI.

To answer your first question. When I duplicate a query all the applied steps are registred, so it does not start from blank. Would you recommend to 'Reference' a query instead of 'Duplicating' it? What is the exact difference between those two and what is the better solution to work to a star model?

Thanks a lot!

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors