Forum Discussion
Limitations getting Data from Direct Query to Local
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
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
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.
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
HeinrichHi 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
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.
