Dataverse query performance question

Hi mbowler 

The performance difference between the two Power Query approaches stems from how query folding and data retrieval are handled when connecting to Dataverse (Dynamics). In method A, Power Query uses the default connector path — CommonDataService.Database("mycomp.dynamics.com") — which retrieves metadata and applies transformations through the OData API layer. This process results in Power Query downloading the full dataset into memory before applying filters or transformations locally, leading to very slow performance, especially for large entities. In contrast, method B uses Value.NativeQuery, which sends a direct SQL-like query (SELECT * FROM my_entity) to the Dataverse endpoint via the TDS (Tabular Data Stream) interface. This means the filtering and data shaping happen server-side, and only the processed results are sent back to Power Query, allowing for significant speed improvements — often seconds instead of minutes. Essentially, method B leverages query folding and server execution, while method A relies on a less efficient OData fetch. The default behavior of Power Query when choosing “Dataverse” as a source still uses the OData path, which prioritizes compatibility and schema safety but sacrifices performance. For large datasets or production refreshes, using Value.NativeQuery with the TDS endpoint is therefore the preferred approach.