Custom connector: refresh in Desktop without clearing cache gives 'The key didn't match any rows'

Hi

Yes, this is a known issue when using custom connectors with dynamic metadata in Power BI. The error 'The key didn't match any rows in the table' typically appears when Power BI's data preview or schema cache is out of sync with the actual structure returned by the connector.

Cause:

Power BI caches metadata (e.g., schema, table names, paths) during navigation. If your connector has dynamic folder or table structures that change between loads, Power BI may try to reference outdated paths or fields, resulting in the key mismatch error.

Workarounds:

1. Implement SupportsDirectQuery = false in the connector
   Forces Power BI to re-fetch metadata during refresh rather than rely on cache.

2. Use Odbc.DataSource-style navigation table
   Implement the NavigationTable pattern using fixed columns like Name, Kind, Item, Data. This helps Power BI recognize stable navigation paths and reduces cache mismatch risk.

3. Add static fallback metadata
   For dynamic tables, return a consistent schema or wrapper that hides volatility from Power BI (e.g., include all possible columns and use nulls where not applicable).

4. Force cache busting programmatically
   Use a query parameter (e.g., version or timestamp) in the URL or function signature so each refresh appears new to Power BI.

5. Document and guide users to "File > Options > Data Load > Clear cache" when errors occur — though not ideal, it's the only workaround when metadata volatility is unavoidable.

There’s no full fix currently unless the dynamic structure is stabilized or wrapped.

freginier