A common question in Power BI projects specific to data retrieval is whether to implement any remaining transformation logic outside the data source in SQL views, within the M queries of the dataset, or both. For SQL Server Analysis Services (SSAS) projects prior to SQL Server 2017, the layer of SQL views was the only option to implement any transformations and some BI teams may prefer this more familiar language and approach. In other scenarios, however, the SQL views may not be accessible or the dataset designer may have a particular strength in M query development relative to SQL. Additionally, given the expanded role of M queries in the Microsoft ecosystem, such as the Common Data Service, as well as support for M query development in Visual Studio, other BI teams may see long-term value in M queries for lightweight data transformation needs.

Ideally, an organization's data warehouse already includes necessary data transformations and thus minimal transformation is required within SQL or M. In this scenario, the M query for the table can simply reference the SQL view of the table, which itself contains minimal to no transformations, and inherit all required columns and logic. As a secondary alternative, the SQL views can be modified to efficiently implement the required logic thus isolating this code to the data source. As a third design option, M queries can implement the required logic and, via query folding, generate a SQL statement for execution by the source. Yet another design option, though less than ideal, is to implement part of the required logic in the SQL view and the remaining logic in the M query.

The guiding principle of the data retrieval process for the import mode dataset is to leverage the resources and architecture of the data source. The M queries of the Power BI dataset, which access the layer of SQL views in the source system, ultimately represent the fact and dimension tables of the data model exposed for report development and ad hoc analysis. This model should address all data transformation needs, thus avoiding the need for DAX-calculated columns and DAX-calculated tables.

Additionally, the data model in Power BI (or Analysis Services) should remain aligned with the architecture and definitions of the data warehouse. If a gap is created by embedding data transformation logic (for example, new columns) into the Power BI dataset that is not present in the data warehouse, plans should be made to eventually migrate this logic to the data warehouse to restore alignment.

In other words, a user or tool should be able to return the same results of a Power BI report based on the Power BI dataset by issuing a SQL query against the source data warehouse. This is particularly essential in environments with other BI and reporting tools built on top of the data warehouse.

As shown in the Dataset Planning Architecture diagram from Chapter 1, Planning Power BI Projects, there are six layers in which data logic can be implemented:

Data retrieval processes should strive to leverage the resources of data sources and avoid or minimize the use of local resources. For example, a derived column implemented within either a SQL Views (layer 3) or within an M Queries (layer 4) which folds its logic to the data source is preferable to a column created by a DAX Calculated Tables and Columns (layer 5). Likewise, if data transformation logic is included within M queries (for example, joins, group by), it's important to ensure these operations are being executed by the source system as described in the Query folding section later in this chapter. These considerations are especially critical for large tables given the relatively limited resources (for example, CPU, Memory) of a Power BI dataset or the On-premises data gateway if applicable.

Additionally, the dimension and fact tables of the Power BI dataset and the DAX measures created should represent a single version for the organization—not a customization for a specific team or project sponsor. Therefore, although the combination of SQL views and M queries provides significant flexibility for implementing data transformations and logic, over time this logic should be incorporated into corporate data warehouses and extract-transform-load (ETL) processes so that all business intelligence tools have access to a common data source.