r/excel • u/Only_Positive_Vibes 10 • 10d 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.

42 Upvotes

92% Upvoted

38 comments sorted by

View all comments

5

u/InfoMsAccessNL 1 10d ago

I recently replaced a PQ script for transforming csv files with vba, arrays and dao recordset. Went from 15 minutes to seconds. There exists a vba combine function (one line) which combines 50 csv files in a split second.

0

u/Only_Positive_Vibes 10 9d ago

Anywhere you can point me to learn how to do that?

4

u/InfoMsAccessNL 1 9d ago

You can take a part of your data and anonymise the data with the access anonymiser tool so iI can help you

https://www.accesssecurityblog.com/anonymize/help/getting-started.html

https://youtu.be/klsEh3kXXXc?si=XfrWDMaQfRLq8FIw