Power BI Desktop contains a very rich set of data source connectors and transformation capabilities that support the integration and enhancement of source data. These features are all driven by a powerful functional language and query engine, M, which leverages source system resources when possible and can greatly extend the scope and robustness of the data retrieval process beyond what's possible via the standard query editor interface alone. As with almost all BI projects, the design and development of the data access and retrieval process has great implications for the analytical value, scalability, and sustainability of the overall Power BI solution.

In this chapter, we dive into Power BI Desktop's Get Data experience and walk through the process of establishing and managing data source connections and queries. Examples are provided of using the Query...

Every time you click on a button to connect to any of Power BI Desktop's supported data sources or apply any transformation to a data source object, such as changing a column's data type, one or multiple M expressions are created reflecting your choices. These M expressions are automatically written to dedicated M documents and, if saved, are stored within the Power BI Desktop file as Queries. M is a functional programming language like F#, and it's important that Power BI developers become familiar with analyzing and later writing and enhancing the M code that supports their queries.

1. Build a query through the user interface that connects to the AdventureWorksDW2016CTP3...

There are two primary components of queries in Power BI: the data source and the query logic executed against this source. The data source includes the connection method (DirectQuery or Import), its privacy setting, and the authentication credentials. The query logic consists of the M expressions represented as queries in the Query Editor and stored internally as M documents.

In a typical corporate BI tool, such as SQL Server Reporting Services (SSRS), the properties of a data source such as the server and database name are defined separately from the queries that reference them. In Power BI Desktop, however, by default each individual query created explicitly references a given data source (for example, server A and database B). This creates an onerous, manual process of revising each query if necessary to change the source...

One of the most valuable features of Power BI is its deep support for real-time and streaming datasets--the ability to provide immediate visibility to business processes and events as this data is created or updated. As Power BI Desktop's data modeling engine reflects the latest SQL Server Analysis Services (SSAS) features, including the enhanced DirectQuery mode for SSAS 2016, it becomes feasible to design DirectQuery models in Power BI Desktop and thus avoid the scalability limitations and scheduled refresh requirements of import-based models.

This recipe walks through the primary steps in designing the data access layer that supports a DirectQuery model in Power BI Desktop. As these models are not cached into memory and dynamically convert the DAX queries from report visualizations to SQL statements, guidance is provided to...

Import is the default data connectivity mode for Power BI Desktop, and the import models created in Power BI Desktop use the same in-memory, columnar compressed storage engine (Vertipaq) featured in SSAS Tabular 2016+ import models. Import mode models support the integration of disparate data sources (for example, SQL Server and DB2) and allow more flexibility in developing metrics and row-level security roles given via full support for all DAX functions.

This recipe describes a process of using M and the Query Editor to develop the queries supporting a standard star-schema analytical model. A staging query approach is introduced as a means of efficiently enhancing the dimensions of a model, and tips are included to use less resources during refresh and to avoid refresh failures from revised source data. More details of these methods are...

The application of precise and often complex filter conditions has always been at the heart of business intelligence, and Power BI Desktop supports rich filtering capabilities across its query, data model, and visualization components. In many scenarios, filtering at the query level via the Query Editor and M functions is the optimal choice, as this reduces the workload of both import and DirectQuery data models and eliminates the need for re-applying the same filter logic across multiple reports.

Although the Query Editor graphical interface can be used to configure filtering conditions, this recipe demonstrates M's core filtering functions and the use of M in common multi-condition filter scenarios.

The columns selected in data retrieval queries impact the performance and scalability of both import and DirectQuery data models. For import models, the resources required by the refresh process and the size of the compressed data model are directly impacted by column selection. Specifically, the cardinality of columns drives their individual memory footprint and memory (per column) correlates closely to query duration when these columns are referenced in measures and report visuals. For DirectQuery models, the performance of report queries is directly affected.

Regardless of the model type, how this selection is implemented also impacts the robustness of the retrieval process. Additionally, the names assigned to columns (or accepted from the source) directly impact the Q & A or natural language query experience. This recipe provides examples...

The transformations applied within Power BI's M queries serve to protect the integrity of the data model and to support enhanced analysis and visualization. The specific transformations to implement varies based on data quality, integration needs, and the goals of the overall solution. However, at a minimum, developers should look to protect the integrity of the model's relationships and to simplify the user experience via denormalization and standardization.

This recipe includes examples of protecting a data model from duplicate values and enhancing the quality of a dimension column via a relationship to a separate data source.

To best follow the duplicate removal...

Business users often extend the outputs of existing reports and data models with additional columns to help them analyze and present data. The logic of these columns is generally implemented through Excel formulas or as calculated DAX columns. A superior solution, particularly if the logic cannot quickly be migrated to a data warehouse or IT resource, is to create the columns via the Query Editor and M language.

Developing custom columns can also significantly enhance the ease-of-use and analytical power of data models and the visualizations they support. In the examples of this recipe, columns are created to simplify the analysis of a customer dimension via existing columns and to apply a custom naming format.

The full power of Power BI's querying capabilities is in the integration of multiple queries via it's merge and append operations. Retrieval processes which consolidate files from multiple network locations or which integrate data from multiple data sources can be developed efficiently and securely. Additionally, the same join types and data transformation patterns SQL and ETL developers are familiar with can be achieved with the M language.

This recipe provides examples of combining sources into a single query and leveraging the table join functions of M to support common transformation scenarios.

• To follow along with this recipe, you can use the Merge Queries and Append...

Setting the data types of columns in Power BI Desktop is usually the final step of queries and has great implications for all layers of the solution including data refresh, modeling, and visualization. Choosing appropriate data types reduces the risk of refresh failures, ensures consistent report results, and provides analytical flexibility to the data model and visualization layers.

This recipe includes four common examples of choosing and defining data types to load to Power BI Desktop. Additional details on data types and the implications of data types for Power BI development are contained in the sections following these examples.

To implement complex and less common data transformation requirements, it's often necessary to browse the M library to find a specific function or review the parameters of a specific function. This short recipe provides a pre-built M query expression you can use to retrieve the M library into a table for analysis in Power BI Desktop. Additionally, an example is provided of visualizing and cross-filtering this table of functions on the Power BI report canvas.

1. Open a new Power BI Desktop file (PBIX) and create a blank Query.
2. Enter the following M code in the Advanced Editor:

• The Query Editor should look like the following...