Power Bi desktop with large data set

Anonymous · ‎02-10-2022

Hi there,

I have a data set that has over 5 million rows, and even though i am able to import the data into Power Bi.

Just clicking on the close and apply takes over 1 hour and making any change just takes too long.

Is there any way to speed up the performance, i could cut down some columns, or even split the data set to use it in 2 seperate

dashboards, but i would prefer if i could have 1 dashboard with all the columns that i currently have.

My company is not looking into changing onto premium so it would be awsume if there are any desktop specifc tips/solution for this situation.

Thanks in advance.

parry2k · ‎02-10-2022

@Anonymous is it like transformation in PQ that you are doing on 5 million rows, not sure what you mean by reconciliation? WHere are you doing this? It is not very clear.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎02-10-2022

@parry2k I would create calculated columns/measures after applying the data from PQ and checking the information that we got from the 5 million data set for specific values that we need. And would that not take forever/break since power bi would have to populate all the calculated columns/measures i have created?

parry2k · ‎02-10-2022

@Anonymous when you working with large tables, I always recommend adding a parameter in PQ, let's call it "Number of Rows" and use some value let's say 1000, and then in your table add a below step which will only load the number of rows based on the value in the parameter, it will speed up the development process, and once everything is working as expected, publish the report and in the Power BI service, change the parameter value to 0 which will load all the rows.

= Table.FirstN(#"Changed Type",  if #"Number of Rows" = 0 then each true else  #"Number of Rows")

So basically when you are developing, you are not FULL ROWS (5 mil).

✨ Follow us on LinkedIn and to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎02-10-2022

That is very useful, but i do need to load the full table for reconcilation purposes. When i build the formula's that do the reconcilation, it takes very long due to the full table but it is a neccesary step before i can implement the limit row factor.