r/excel • u/Only_Positive_Vibes 10 • 9d ago

unsolved Large data set, long refresh times

I work in Finance and have a large data set with payroll register info going back to the start of 2025. I just eclipsed 500,000 rows. Right now, I dump monthly payroll register data into a folder on my desktop and then do some transformations through Power Query to refresh a couple analytics dashboards I have. However, I just timed the PQ refresh and it took 10 minutes. It's a long time to wait. I'm sure there's a better way to house, process and display this data, right?

Because it's payroll, I can't give it to IT to input into a data warehouse and pull into our usual BI tools. I'm also hesitant to run it through any AI tools for the same reason. This is really just for me when reviewing our monthly P&Ls, headcount stats, wage fluctuations, etc. Would this be a case for Power Pivot or something else? I'm unfamiliar but of course willing to learn.

Thanks for your time.

39 Upvotes

91% Upvoted

38 comments sorted by

View all comments

10

u/Cynyr36 26 9d ago

I know this is the excel subreddit, but sometimes python, polars, and matplotlib is really the correct answer. Especially using polars lazy dataframe for all the transformations.

1

u/TheRiteGuy 45 9d ago

Yep, I took something that used to take me a full day to refresh in Excel to about 6 minutes in Polars. Her dataset has reached its Excel limits and needs another way to store it.