For DirectQuery datasets, every M query is folded to exclusively utilize the resources of the single data source. Therefore, certain M functions and query logic that lack an equivalent SQL expression for the given data source, such as Oracle or Teradata, are not supported. In these scenarios, the dataset designer can develop alternative M queries that produce the same target data structure and are supported by the source system or implement the necessary logic within the layer of SQL views supporting the dataset.
An additional and fundamental limitation to the scope of M queries for DirectQuery datasets is the impact on query performance and user experience. Since the SQL statements representing M queries must be executed by the source system during report viewing sessions, common transformations such as converting data types and sorting tables can cause significant performance degradation.
Additionally, a high volume of sub-optimal SQL queries passed from Power BI reports can quickly drive up the resource usage of the source system. Therefore, although it's often technically possible to implement similar data transformation logic in the SQL views and M queries of DirectQuery datasets as with import mode datasets, the performance and resource implications of these transformations frequently prove unacceptable.