Limitations getting Data from Direct Query to Local

Hi Heinrich ,

DirectQuery itself doesn’t cache data locally except for query results, so you cannot “copy all data” fully offline. To get only newest data locally, you must switch to Import mode with incremental refresh configured on a datetime column. This lets Power BI cache just recent data locally and refresh it incrementally.

Hello Heinrich 

Using Composite Model & Manual Append Logic

If your model supports composite mode,

Keep your main table in DirectQuery.

Create a new table with filtered import of "newest data" as mentioned above.

Use DAX to create measures or append tables that reference only the import portion when needed (e.g., for faster visuals, small summary views, etc.).

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Hi Heinrich ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below options.

1. Partition with Import Table via Incremental Refresh

If you are using Power BI Premium or PPU, you can use Incremental Refresh:

Set up a table as Import, not DirectQuery. Use a RangeStart / RangeEnd parameter to filter "newest" data (like LastModifiedDate >= RangeStart).

This pulls only the new data every time and keeps historical data locally.

Note: Only works in Premium (or PPU). Doesn’t apply if you are stuck with Pro or Shared capacity.

2. Create a Dual Table (Hybrid Model)

If your model is Composite (DirectQuery + Import), you can: Create a small Import table to hold only the latest data (e.g., last 7 days).

Use a date filter or TOP N by timestamp in Power Query to only pull new rows.

Steps:

Duplicate the DirectQuery table as an Import query.

Apply a filter like: WHERE Date >= TODAY() - 7 or TOP N by CreatedDate DESC. Load it as Import, this gives you a small local copy of just the new data.

Works even in Power BI Pro.

3. Use a Parameter to Filter “Latest” Data in Import

If you want more control: Create a Date/ID parameter manually (like MaxDateLoaded).

Use it in Power Query for the Import table:

Source = Sql.Database(...),
Filtered = Table.SelectRows(Source, each [Date] > MaxDateLoaded)
Update the parameter before refresh to simulate incremental logic.

Note: This doesn’t automatically persist the old data, but you can append manually if you store snapshots.

4. Maintain Snapshot via Append Queries (Manual or via Refresh)

Another strategy: Create a local table to hold historical snapshot (via Import). Periodically (daily or on demand), query DirectQuery for newest rows only (last 1 day/hour/etc.). Append to the local snapshot. You can simulate this with: A Power BI desktop solution. Manual refresh or scripted refresh (via Power BI Service or Power Automate).

Please refer Microsoft articles and community threads.

Solved: Getting only the latest database data instead of d... - Microsoft Fabric Community

PowerQuery Question: Query Folder - only keep most recent file - Microsoft Community

Best practices when working with Power Query - Power Query | Microsoft Learn

Solved: Last record based on latest date - Power Query - Microsoft Fabric Community

Solved: Include only Latest 3 months of data - Microsoft Fabric Community

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you