DirectQuery stale data

Hi markmsc 

What you're experiencing is a subtle caching behavior in Power BI Service related to DirectQuery and the way Power Query transformations are applied before the data reaches the model. Even though DirectQuery is designed to fetch live data, when you apply Power Query steps—such as filtering on a column—the service can cache query results for performance optimization, especially when the query is complex or the data is filtered. This caching can cause the data shown in reports to appear stale, updating only periodically rather than instantly, which explains why your filtered table shows outdated data while the other table without such filters returns current data immediately.

When you remove the filter on the audit column in Power Query, the query becomes simpler or more straightforward, causing the service to bypass or reduce caching, and thus data refreshes correctly with current values. The caching mechanism here is intended to reduce load on the source system and improve performance, but it can cause confusion when data freshness is critical.

Unfortunately, Power BI Service doesn’t provide direct controls to disable this caching behavior for queries involving Power Query filters on DirectQuery sources. The best practice is to minimize complex Power Query transformations on DirectQuery tables or move such filtering logic into the source database as a view or stored procedure, which ensures the service queries live data directly without intermediate caching. Alternatively, periodically refreshing the dataset or adjusting incremental refresh policies might help mitigate the effect. Understanding this nuance helps explain why only the filtered table’s data appears stale while the other remains current.