It's a quiet trap, and it's been sitting right in plain sight: hidden table filters can silently poison your Power Query reports. The user who posted this question has identified a real and frustrating behavior, Power Query respects the filtered state of an Excel table, meaning that if anyone on the team leaves a column filter engaged, your data model will refresh with incomplete data. That is not a bug, but it is a design choice that demands a disciplined workflow or a deliberate workaround.
The practical consequence is straightforward: your report is only as reliable as the last person who touched the source table. In a multi-user environment, that is a fragile foundation. The fix, however, is not to abandon Power Query or to micromanage every colleague's filter habits. The solution lives inside Power Query itself. Instead of referencing the table directly, you can reference the worksheet's used range or define a named range that bypasses table-level filters. Alternatively, you can load the table with the "Data Source Settings" option to ignore table filters, or write a simple M query that explicitly pulls the full table range regardless of applied filters. These approaches shift control from the spreadsheet's UI to your query logic, where it belongs.
What this story really reveals is a broader truth about the transition from manual spreadsheets to automated data models. Users are right to expect that their query layer should be insulated from accidental human interference. That expectation is reasonable, and it points toward a design principle: your data ingestion should never assume that a human-visible filter is the same as a data filter. Power Query can be configured to meet that expectation, but it does not default to it. That is worth knowing, and worth teaching to every team member who builds reports on shared tables.
The takeaway here is not about blaming the tool or the user. It is about understanding the boundary between the spreadsheet's display and the model's data. Treat your source tables as raw material, not as formatted views. Use Power Query's native options to strip filters at the source, or adopt a staging table that your team does not filter. Either way, make the data pipeline resilient to human habits. That is the difference between a report you trust and one that quietly misleads.