The following are required to complete the recipes in this chapter:

• Power BI Desktop.
• SQL Server 2022 or newer with the AdventureWorksDW2022 database installed. See the Installing SQL Server and the AdventureWorks Database and Installing and Configuring Power BI Desktop sections in Chapter 1, Installing and Licensing Power BI Tools.

The output of Power Query (M) queries can either be loaded (imported) as compressed in-memory local tables or can merely define source tables for a data source to use via a DirectQuery connection. The import storage mode is the default and preferred option for most scenarios, as this delivers the best possible performance while also providing greater modeling flexibility, with more DAX functions supported and easier integration of distinct sources. Import is the default storage mode for Power BI Desktop. The collection of imported tables and DirectQuery tables is referred to as a semantic model.

Import semantic models created in Power BI Desktop use the same in-memory, columnar-compressed storage engine (VertiPaq) featured in Analysis Services’ Tabular 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, via...

One of the most valuable features of Power BI is its deep support for real-time and streaming semantic models, with the ability to provide immediate visibility to business processes and events as this data is created or updated. As Power BI has become Microsoft’s flagship semantic modeling tool, with exclusive features not available in Azure Analysis Services or SQL Server Analysis Services, it has become feasible to design both DirectQuery models and composite models which utilize a mixture of DirectQuery and import storage modes on a per table basis.

The three most common candidates for DirectQuery or composite model projects are as follows:

• The data model would consume an exorbitant amount of memory if all tables were fully loaded into memory. Even if the memory size is technically supported by large Power BI Premium capacity nodes, this would be a very inefficient and expensive use of company resources, as most BI queries only access aggregated...

Power BI dataflows are a feature within the Microsoft Power BI service that allows users to transform and combine data from various sources, perform data transformation and cleansing operations, and then store the results in inexpensive Azure Blob Gen2 Storage.

Dataflows also use the Power Query M language and are constructed using a web-based version of Power Query Editor. In effect, dataflows are cloud-based versions of queries that you would otherwise create in Power BI Desktop.

Getting ready

To prepare for this recipe, you will need a Power BI Pro license or trial license. See the Thinking about Licensing recipe from Chapter 1, Installing and Licensing Power BI Tools. Create a new workspace in the Power BI service. This workspace should be a Pro workspace and not a Fabric or Fabric trial workspace. Fabric workspaces include the Dataflows Gen2 capabilities, which are constructed almost identically to dataflows.

In addition, you should complete...

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 Power 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 to re-apply the same filter logic across multiple reports or visualizations.

Although Power Query Editor’s 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 M expression queries constructed in this recipe are intended to highlight some of the most common filtering use cases.

Note that applying data transformations as part of a data warehouse ETL or ELT (extract-load-transform...

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 vary based on data quality, integration needs, and the goals of the overall solution. At a minimum, developers should seek to protect the integrity of the model’s relationships and simplify the user experience via denormalization and standardization. Additionally, developers should converse with the owners of data sources to determine if required transformations can be implemented in the source or made available via SQL view objects.

This recipe demonstrates how to protect a model from duplicate values within the source data that can prevent the formation of proper relationships within the data model, which may even result in query failures. While a simple scenario is used, this recipe demonstrates scenarios you may run into while attempting to merge...

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 be migrated to a data warehouse or IT resource, is to create the columns via Power Query Editor and the 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 this recipe, columns are created to apply a custom naming format and simplify the analysis of a customer dimension via existing columns.

Getting ready

To get ready for this recipe, import the DimCustomer table from the AdventureWorksDW2022 database by doing the following:

1. Open Power BI Desktop and choose Transform data from the ribbon of the Home tab to open Power Query Editor.
...

The full power of Power BI’s querying capabilities is in the integration of distinct queries representing different data sources, via its merge and append transformations. Retrieval processes that consolidate files from multiple network locations or integrate data from multiple data sources can be developed efficiently and securely. Additionally, the same join types and data transformation patterns that 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.

Getting ready

To follow along with this recipe, you can use the Merge Queries and Append Queries icons on the Home tab of Power Query Editor to generate the join expressions used in this recipe. However, as joining queries is fundamental to the retrieval process, it is recommended to learn how to use the...

Well before any semantic models and reports are developed, and especially before business stakeholders begin consuming the content to derive insights, it’s a good practice to assess and validate the quality of the source data. Data quality profiling exercises can reveal the presence of various issues, ranging from null or blank values in certain columns to duplicate rows, to the lack of unique or identifying (primary key) columns. The findings from this exercise inform decisions on whether the source system data itself can be cleansed or whether the BI solution will address these issues, via the various Power Query (M) data cleansing functions available.

The topic of data quality deals with the overall utility of semantic models, as well as the ability to easily process and use the data for certain purposes, including analytics and reporting. Data quality is an essential component of data governance, ensuring that business data is accurate, complete...

The Power Query M engine is an extremely powerful and fast data transformation and data preparation engine used across an array of products, including:

• Excel for Windows
• Excel for Mac
• Power BI
• Power Apps
• Power Automate
• ADF
• SSIS
• SQL Server Analysis Services
• Dynamics 365 Customer Insights

While both fast and powerful, there are times when you may find that a particular query is not as performant as desired. In these instances, Query Diagnostics can help you pinpoint problematic expressions and better understand what Power Query is doing in order to identify areas for query optimization. This recipe demonstrates how a user can use Query Diagnostics to troubleshoot a query and identify how the query might be optimized to be more performant.

Getting ready

To get ready, import the FactCurrencyRate table from the AdventureWorksDW2022 database by doing the following:

1. Open Power BI Desktop...

The core M library comes with hundreds of different functions to aid in data connectivity, data cleansing, and data transformation. However, one of the strengths of the M language is its ability to be easily extended with custom functions.

In this recipe, we add a simple custom function as an extension of the core M library of functions.

Getting ready

To prepare for this recipe, we first need to identify and/or create a Custom Connections folder for Power BI Desktop. Look in your Documents directory for the current user. If the folder Microsoft Power BI Desktop does not exist, create it. Navigate to the Microsoft Power BI Desktop folder, and if the folder Custom Connectors does not exist, create it.

How to extend the core M library

To implement this recipe, follow these steps:

1. Create the following text file and save the file as Cookbook3.pq in the [Documents]\Microsoft Power BI Desktop\Custom Connectors folder.

   /...

The ability to ingest and access data is crucial to every BI project. In Power BI, this data ingest and access is driven by a powerful functional language and query engine, M. The Power Query M language provides a robust, scalable, and flexible engine for data retrieval, cleansing, and transformation.

In this chapter, we dove into Power BI Desktop’s data transformation experience and walked through the process of establishing and managing data source connections and queries. We explored both import and DirectQuery storage modes, as well as creating reuseable queries within the Power BI Service called dataflows. We then demonstrated powerful data transformation techniques, including filtering and cleansing data, creating custom columns, and combining and merging multiple queries. Finally, we demonstrated how to profile data, diagnose non-performant queries, and how to extend the core M library of functions.

In the next chapter, we leverage the knowledge gleaned...