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 written for you by the editor. You will learn more about M later in this chapter.
Finally, the last important consideration to understand is that all transforms that are created within the editor are stored in the Query Settings pane under a section called Applied Steps. Why is this important to know? The Applied Steps section has many features, but here are some of the most critical to know for now:
- Deleting transforms: If you make a mistake and need to undo a step, you can click the Delete button next to a step.
- Modifying transforms: This can be done with any step that has a gear icon next to it.
- Changing the order of transforms: If you realize that it is better for one step to execute before another one, you can change the order of how the steps are executed.
- Clicking on any step prior to the current one will allow you to see how your query results would earlier in the process.
With this understanding, you will now get hand-on with applying several basic transforms inside the Power Query Editor. The goal of these first sets of example is to get you comfortable with the Power Query user interface before the more complex use cases are covered.