Limitations getting Data from Direct Query to Local

[Heinrich]

Hello

Not all data is copied from Direct Query to Local.
The copy works but there is no way the get all the data.

I can not use Dataflows or SQL Database.

Could it be possible to get only the newest data and if yes how?

Thanks
Heinrich

[v-dineshya]

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

[v-dineshya]

Hi @Heinrich ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[Heinrich]

Hello @v-dineshya 
Hello @pankajnamekar25 
Thank you very much for your detailed reply it is very kind
Solution 1: Is not possible as we do not have the licences
Solution 2: I created a 2nd table in Import Mode. But I start to believe that the data is from 2025 and not from 2024 and from this June and not May as it is shown. Because, when I look at the days and month they procceed and there are only last and few data. 
Is it possible that data in direct query is limited and changed.
I am not in control of the Direct Query Database.
How can I check for these?
Regards
Heinrich

[v-dineshya · Accepted solution]

Hi @Heinrich ,

1. DirectQuery connections can be restricted in the following ways:

Row-level filters applied at the data source level. Row/column limitations in the view/query that the DirectQuery model is based on. Data source may only expose recent data. Power BI itself does not limit DirectQuery by date, but it only queries what is needed for the current visual/page.

2. Verify the True Range of DirectQuery Data

A. Create a summary measure or table. Use DAX to detect the min/max dates in the DirectQuery table.

Min Date = CALCULATE(MIN('Table'[Column]))
Max Date = CALCULATE(MAX('Table'[Column]))

Note: Place this in a card visual or a table to show the actual temporal range of your DirectQuery data.

B. Build a simple DirectQuery matrix

Create a matrix like this: Rows: Year, Month from the date field & Values: Count of rows. It will visually show if older data (e.g. 2024 or pre-May 2025) is missing.

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

[v-dineshya]

Hi @Heinrich ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @Heinrich ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[pankajnamekar25]

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.

[Heinrich]

Hello @pankajnamekar25 
Hello @BhavinVyas3003 
Thank you for your input. 
I can not change the mode. The only solution would be to copy the data. Managed to copy the data but it does only copy 2024.

I created the table using a DAX

NER = SUMMARIZE(
'CQD',
'CQD'[Start Time],
'CQD'[PSTN NER Good Percentage],
'CQD'[PSTN Trunk FQDN]
)

And it does only copy 2024.

What do you mean with incremental refresh. The imported table exists but it seems that only 2024 is imported.
Regards
Heinrich

[BhavinVyas3003]

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.