The Power Query Editor is the primary tool that you will utilize for applying data transformations and cleansing processes to your solution. This editor can be launched as part of establishing a connection to your data, or by simply clicking Edit Queries on the Home ribbon of the Power BI Desktop. When the Power Query editor is opened, you will notice that it has its own separate environment for you to work in. The environment encapsulates a user-friendly method for working with all of the queries that you will define. Before you dive deep into the capabilities of the Power Query Editor, let's first start by doing an overview of the key areas that are most important:

Following the numbered figures, let's review some of the most important features of the Power Query Editor:

• New Source: This launches the same interface as the...

Applying data transformations within the Power Query Editor can be a surprisingly simple thing to do. However, there are few things to consider as we begin this process. The first is that there are multiple ways to solve a problem. As you work your way through this book, the authors have tried to show you the fastest and easiest methods of solving the problems that are presented, but these solutions certainly will not be the only ways to reach your goals.

The next thing you should understand is that every click you do inside the Power Query Editor is automatically converted into a formula language called M. Virtually all the basic transforms you will ever need can be accomplished by simply interacting with the Power Query Editor user interface, but for more complex business problems there is a good chance you may have to at least modify the M queries that are...

Now that you should be more comfortable working within the Power Query Editor, let's take the next step in working with it. Often, you will find the need to go beyond these basic transforms when dealing with data that requires more care. In this section, you will learn about some of the more common advanced transforms that you may have a need for, which include Conditional Columns, Fill down, Unpivot, Merge Queries, and Append Queries.

Using the Power Query Conditional Columns functionality is a great way to add new columns to your query that follow logical if/then/else statements. This concept of if/then/else is common across many programming languages, including...

R is a very powerful scripting language that is primarily used for advanced analytics tools, but also has several integration points within Power BI. One such integration is the ability to apply business rules to your data with the R language. Why is that important? Well, with this capability you can extend beyond the limits of the Power Query Editor and call functions and libraries from R to do things that would not regularly be possible. In the next two sections, you will explore how to set up your machine to leverage R within Power BI and then walk through an example of using an R Script transform.

The Power Query Editor is the user interface that you have now learned is used to design and build data imports. However, you should also know that every transform you apply within this editor is actually, quietly and behind the scenes, writing an M query for you. The letter M here is a reference to the languages data mashup capabilities.

For simple solutions, it is unlikely that you will ever need to even look at the M query that is being written, but there are some more complex cases where it's helpful to understand how to read and write your own M. For the purposes of this book, covering just the Power BI essentials, you will learn how to find the M query editor within your solution and then understand how to read what it is doing for you. For the purposes of this example, you can open up any previously built example, however the screenshot used here...

In this chapter, you learned that the Power Query Editor is an extremely powerful tool for applying business rules to incoming data. Implementing data cleansing techniques can be as simple as right-clicking on a column, or more complex such as when building a Conditional Column. While the Power Query Editor does have a vast library of transforms available, you also learned that you can tap into the capabilities of R to extend what's possible when designing queries. Finally, this chapter also helped you learn that the decisions you make while building your queries can impact Query Folding, which can be incredibly important for the performance of your queries.