Data Preparation in Power Query Editor

In the previous chapters, we discussed various layers in Power BI and went through some scenarios. By now, we know Power BI is not only a reporting tool. Power BI is indeed a sophisticated all-round Business Intelligence (BI) technology, with the flexibility to be used as a self-service BI tool that supports many BI aspects such as Extract, Transform, and Load (ETL) processes, data modeling, data analysis, and data visualization. As a powerful BI tool, Power BI is improving every day, which is fantastic. Microsoft’s Power BI development team constantly brings new ideas to this technology to make it even more powerful. Data preparation and ETL activities are the BI areas in which Power BI is great for using the Power Query Editor in Power BI Desktop. Power Query Editor is the dedicated tool in Power BI to write Power Query expressions. Power Query is available in Excel and a few other Microsoft data platform products. This chapter looks...

Introducing the Power Query M formula language in Power BI

Power Query is a data preparation technology offering from Microsoft to connect to many different data sources from various technologies, enabling businesses to integrate data, transform it, make it available for analysis, and get meaningful insights from it. Power Query can currently connect to many data sources.

It also provides a custom connectors software development kit (SDK) that third parties can use to create their data connectors. Power Query was initially introduced as an Excel add-in that quickly became a vital part of the Microsoft data platform for data preparation and transformation.

Power Query is currently integrated with a few Microsoft products such as Dataverse (also known as Common Data Service (CDS)), SQL Server Analysis Services Tabular models (SSAS Tabular), SQL Server Integration Services (SSIS), Azure Data Factory (ADF), and Azure Analysis Services (AAS), as well as Power BI and Excel. Therefore...

Introduction to Power Query Editor

In Power BI Desktop, Power Query is available within Power Query Editor. There are several ways to access Power Query Editor, outlined as follows:

• Click the Transform data button from the Home tab, as illustrated in the following image:

Figure 3.9: Opening Power Query Editor from the ribbon in Power BI

• We can navigate directly to a specific table query in Power Query Editor by right-clicking the desired table from the Fields pane and then clicking Edit query, as shown in the following image:

Figure 3.10: Navigating directly to a specific underlying query in Power Query Editor

Power Query Editor has the following sections:

1. The Ribbon bar
2. The Queries pane
3. The Query Settings pane
4. The Data View pane
5. The Status bar

The following image shows the preceding sections:

Figure 3.11: Sections of Power Query Editor

The following sections go through some...

Introduction to Power Query features for data modelers

This section looks at some features currently available within Power Query Editor that help data modelers identify and fix errors quickly. Data modelers can understand data quality, statistics, and data distribution within a column (not the overall dataset). For instance, a data modeler can quickly see a column’s cardinality, how many empty values a column has, and so forth.

As previously mentioned, the information provided by the Column quality, Column distribution, and Column profile features is calculated based on the top 1000 rows of data (by default), which sometimes leads to false information. It is good practice to set Column profile to get calculated based on the entire dataset for smaller amounts of data. However, this approach may take a while to load the column profiling information for larger amounts of data, so be careful while changing this setting if you are dealing with large tables.

To change the...

Understanding query parameters

One of the most valuable features is the ability to define query parameters. We can then use defined query parameters in various cases. For instance, we can create a query referencing a parameter to retrieve data from different datasets, or we can parameterize filter rows. With query parameters, we can parameterize the following:

• Data Source
• Filter Rows
• Keep Rows
• Remove Rows
• Replace Rows

In addition, we can load the parameters’ values into the data model to reference them from measures, calculated columns, calculated tables, and report elements if necessary.

We can easily define a query parameter from Power Query Editor, as follows:

1. Click Manage Parameters.
2. Click New.
3. Enter a name.
4. Type in an informative description that helps the user understand the parameter’s purpose.
5. Checking the Required box makes the parameter mandatory.
6. Select a type from the...

Understanding custom functions

In many cases, we are in a situation where we require repetitive transformation logic. In such cases, creating a custom function that takes care of all the calculation logic needed makes sense. After defining the custom function, we invoke it many times. As stated in the Introducing the Power Query M formula language in Power BI section, under Function value, we create a custom function by putting the parameters (if any) and their data type in parentheses, along with the output data type and the goes-to symbol =>, followed by the definition of the function.

The following example shows a straightforward form of a custom function that gets a date input and adds one day to it:

SimpleFunction = (DateValue as date) as date =>
Date.AddDays(DateValue, 1)

We can invoke the preceding function as follows:

SimpleFunction(#date(2020,1,1))

The result of invoking the function is 2/01/2020.

We can define a custom function as an inline...

Summary

This chapter introduced different aspects of the Power Query M formula language and looked at how we can use Power Query Editor. We also looked at real-world scenarios and challenges that can directly affect our productivity and learned how to manage our data preparation step more efficiently.

The next chapter discusses getting data from various data sources, connection modes, and how they affect our data modeling.

Join us on Discord!

Join The Big Data and Analytics Community on the Packt Discord Server!

Hang out with 558 other members and enjoy free voice and text chat.

https://packt.link/ips2H