Incremental Refresh Error - "The key didn't match any rows in the table"

[manoj_0911]

Hello Power BI Community,

I’m facing an issue while setting up incremental refresh in Power BI for a Dashboard that is connected to SQL Server Tables. The error message I’m getting is:

"The key didn't match any rows in the table."

"Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Schema = \"dbo\", Item = \"DATE_TIME\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"Schema\", \"Item\", \"Kind\"}, {})"}}],"exceptionCulprit":1}}}
Cluster URI: WABI-US-EAST2-B-PRIMARY-redirect.analysis.windows.net
Activity ID: ae062e1f-9849-4007-bad8-234f228b9a05
Request ID: 1f7d528c-326f-4079-a252-b63954230ca4
Time: 2025-02-03 12:52:48Z"

Dataset Details

I have created the dashboard by connecting to multiple tables whihc are joined together, including AG2_STATUS_SUBHOUR and DATE_TIME. This is how i have joined the tables:

FROM
AG2_STATUS_SUBHOUR ag2
JOIN DATE_TIME sdt ON (sdt.DATE_TIME_KEY = ag2.DATE_TIME_KEY)
JOIN PERSONS p ON (p.USER_ID = ag2.USER_ID AND p.ORGANIZATION_ID = ag2.ORGANIZATION_ID)
LEFT OUTER JOIN TEAM_MEMBERS tm ON (tm.USER_ID = p.USER_ID)
LEFT OUTER JOIN TEAMS t ON (t.TEAM_KEY = tm.TEAM_KEY)
LEFT OUTER JOIN GROUP_MEMBERS gm ON (ag2.USER_ID = gm.USER_ID)
LEFT OUTER JOIN GROUPS g ON (gm.GROUP_ID = g.GROUP_ID)
LEFT OUTER JOIN DIVISIONS d ON (d.DIVISION_ID = p.DIVISION_ID)

I set up incremental refresh using the TXN_DATE column (which comes from the DATE_TIME table), but when I refresh the dataset in Power BI, I get the above error.

My Question

1️⃣What is the best field to use for incremental refresh in my scenario?
2️⃣Why does Power BI throw this error even though the data exists?
3️⃣Are there any known workarounds to fix this issue?

Would appreciate any insights! 🚀

Thanks in advance! 😊

[Anonymous]

Hi @manoj_0911,

Thanks for the reply form 3CloudThomas.

Let me answer your question one by one:

1. What is the best field to use for incremental refresh in my scenario?

   In your case, you mentioned using the TXN_DATE column from the DATE_TIME table.

   • Ensure the column (TXN_DATE) is of type date or datetime.
   • Date/DateTime column should be part of the fact table (e.g., AG2_STATUS_SUBHOUR) or a table that is consistently joined in your query.
   • The column should have no null values in the table.
   • If TXN_DATE is not directly available in the fact table, ensure it is correctly joined from the DATE_TIME table.
2.  Why does Power BI throw this error even though the data exists?The error occurs because Power BI is unable to locate the specified column or table during the incremental refresh process.There are many reasons for this error, incorrect column name reference or, as mentioned above, the column type is not a date or datetime type.Check whether the DATE_TIME table is included in the Power Query transformation and loaded into the dataset.If you are using an on-premises data gateway, make sure the gateway has the required permissions to access the SQL Server table.
3. Are there any known workarounds to fix this issue?Troubleshoot error one by one as mentioned in point1 and point2.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[manoj_0911]

JOIN AG2_STATUS_SUBHOUR and DATE_TIME USING DATE_TIME_KEY

LIKE WISE OTHER JOINS

FOR INCREMENTAL REFRESH I'M USING UPDATE DATE_TIME

WHILE REFRESHING IN POWERBI SERVER GETTING THIS ERROR

[Anonymous]

HI @manoj_0911,

Let me sum up:

1). Your AG2_STATUS_SUBHOUR table has DATE_TIME_KEY values like 1737529200, 1737531900, but in DATE_TIME, the values seem different (e.g., 1728198000).

If Power BI is trying to filter by TXN_DATE, but there is no corresponding DATE_TIME_KEY in the DATE_TIME table, the refresh will fail.

2). Is the TXN_DATE column correctly filters data?

3). Is the DATE_TIME_KEY in both tables is the same data type (INTEGER or BIGINT)?
If one is stored as a string in Power BI and an integer in SQL, the join may not work properly.

4). Does the query folding work correctly?

Incremental refresh requires query folding (where Power BI pushes filters back to SQL Server).
If your query transformations prevent query folding, Power BI may attempt to filter in memory, causing missing data.

In Power Query, right-click on the last transformation step → "View Native Query" to confirm SQL execution.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[manoj_0911]

Thank you for your reply , can i do inner join of AG2_STATUS_SUBHOUR and DATE_TIME TABLE because this is the join logic i need to use "

FROM
AG2_STATUS_SUBHOUR ag2
JOIN DATE_TIME sdt ON (sdt.DATE_TIME_KEY = ag2.DATE_TIME_KEY)
JOIN PERSONS p ON (p.USER_ID = ag2.USER_ID AND p.ORGANIZATION_ID = ag2.ORGANIZATION_ID)
LEFT OUTER JOIN TEAM_MEMBERS tm ON (tm.USER_ID = p.USER_ID)
LEFT OUTER JOIN TEAMS t ON (t.TEAM_KEY = tm.TEAM_KEY)
LEFT OUTER JOIN GROUP_MEMBERS gm ON (ag2.USER_ID = gm.USER_ID)
LEFT OUTER JOIN GROUPS g ON (gm.GROUP_ID = g.GROUP_ID)
LEFT OUTER JOIN DIVISIONS d ON (d.DIVISION_ID = p.DIVISION_ID)

" I also tried with UPDATE_DATETIME field from AG2_STATUS_SUBHOUR, BUT STILL GETTING ERROR

[Anonymous]

Hi @manoj_0911,

Now I think you'd better configure your incremental refresh from beginning.

Or I want to confirm with you that if you have converted the datetime value into integer since I have seen that you configure the two parameters with date time value but you have integer value in your DATE_TIME TABLE.

Please check the doc for detail on how to convert datetime value to integer.

Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[manoj_0911]

CAN YOU PLEASE HELP HOW TO CREATE THESE JOINS IN POWERBI 

FROM
AG2_STATUS_SUBHOUR ag2
JOIN DATE_TIME sdt ON (sdt.DATE_TIME_KEY = ag2.DATE_TIME_KEY)
JOIN PERSONS p ON (p.USER_ID = ag2.USER_ID AND p.ORGANIZATION_ID = ag2.ORGANIZATION_ID)
LEFT OUTER JOIN TEAM_MEMBERS tm ON (tm.USER_ID = p.USER_ID)
LEFT OUTER JOIN TEAMS t ON (t.TEAM_KEY = tm.TEAM_KEY)
LEFT OUTER JOIN GROUP_MEMBERS gm ON (ag2.USER_ID = gm.USER_ID)
LEFT OUTER JOIN GROUPS g ON (gm.GROUP_ID = g.GROUP_ID)
LEFT OUTER JOIN DIVISIONS d ON (d.DIVISION_ID = p.DIVISION_ID)

[Anonymous]

Hi @manoj_0911,

I have found that you filter on UPDATE DATE_TIME column, however, I don't see this column in any of the screenshot you provide.

Have you ever change the structure of data table?

I suggest you refresh the data source and try again.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[manoj_0911]

UPDATE DATE_TIME column IS IN AG2_STATUS_SUBHOUR TABLE

[Anonymous · Accepted solution]

Hi @manoj_0911,

I have seen that you have a duplicated case that has been resolved here, I will mark here to close this case so that other users who may have the similar issue could be directly navigated there.

Thanks for your cooperation.

[Anonymous]

Incremental refresh works best on a single table, like a fact table that has transaction rows. The message seems to indicate the key (date) does not have matching rows in the LEFT join, thus returning rows with Null in the Date key column.