There's a quiet frustration that builds when you know the answers are sitting in your data, but the tools you're using refuse to let them out. That's exactly where this reader finds themselves: 150,000 rows across three years, messy daily production sheets, and a power query that turns every refresh into a waiting game. The instinct to build a reporting tool is right. The approach, however, needs a reset, not because the data is unmanageable, but because the pipeline is being asked to do something it was never designed for.
The real issue here isn't the data volume. Fifty thousand rows per year is small enough to handle in a modern analytics tool without breaking a sweat. The problem is that Power Query is acting as both the extraction layer and the presentation layer. Every time you load, it's redoing the heavy lifting: parsing, cleaning, reshaping. And then you're asking it to display that in a way that's interactive. That's a double duty that will slow down even modest datasets. The fix is to separate the work, let Power Query do the heavy lifting once, then land the cleaned result in a proper data model or a dedicated reporting tool that can handle the slicing and dicing without reprocessing everything from scratch.
What this reader is really asking for is a shift from "pulling data" to "structuring insights." They want part efficiency by machine type, and they want to see it by shift manager too. Those are straightforward dimensions, but they'll only be useful if the data is shaped for analysis first. That means thinking in terms of star schemas: fact tables for production events, dimension tables for machines, shifts, managers, and dates. It's not glamorous work, but it's the difference between a dashboard that answers questions in seconds and a spreadsheet that still spins its wheels at lunchtime. The good news is that 150,000 rows is trivially small for tools like Power BI, Tableau, or even a well-structured Excel data model. The bottleneck is not the data, it's the architecture.
So here's the practical path forward: stop trying to make Power Query the final destination. Use it to clean and shape the data once, then load it into a proper data model where relationships are defined and measures are calculated. If the load time is still painful, that's a signal to look at the query steps, are you removing columns and filtering rows before or after the load? Are you doing calculations in Power Query that could happen in the data model instead? These are solvable problems, but they require a mindset shift. You're not building a report. You're building a foundation for decision-making. That distinction is what turns a frustrating chore into a tool that actually reveals the trends your production team has been missing. Start with a single month, model it properly, and let the speed speak for itself.