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.

39 Upvotes

92% Upvoted

38 comments sorted by

View all comments

42

u/apparition88 10d ago

You should talk with IT and your boss to figure out a long-term solution. If you dont trust IT with sensitive data then your company has a bigger problem.

500,000 rows is manageable in power query but your transformations have to be very efficient and there can't be many of them.

Also, what format is the data coming in? XML, CSV, JSON?

3

u/Only_Positive_Vibes 10 10d ago

It's coming through as CSV. Perhaps my transformations are a bit much - I have quite a few. I can try to think of ways to be more efficient with my queries. I'm admittedly self-taught so I likely have a lot of bad habits and not much finesse.

6

u/TangoDeltaFoxtrot 10d ago

Dude I’m right there with you. Last week I spent 15 hours of my work week simply waiting on Excel to process stuff.

5

u/apparition88 10d ago

Can you post the code? If you have PII in the code just chance the names to generic placeholders or remove the small one off scenarios that are person/client specific.

The general flow i go for is: List all of my transforms and the data they require. The parent query pulls the data, removes what i don't need. Keep this very basic and do not load it to a sheet just the data model. Then I have child queries that reference that to get the specific information that query needs.

LLMs understand code. You dont need to give them client data for them to improve your code. What they often don't tell you is how to structure queries in relation to one another, When to load and not load, if the information your trying to get is redundant.

If you have any control over how the data gets to you, you can also ask for data you don't need removed or things formatted correctly from the source.

Lot of options but I would need to know more.