This article is about how to learn Power Query and some tips at the end.

Quickly, what is Power Query?

Power Query queries data and manipulates the data into something useful. It doesn’t change the source data but is loaded into Excel and Power BI (can also be used by Flow and SSIS).

In the Power Query editor, you create queries which are a series of applied steps or transformations that manipulate a data structure: To give you an idea: with the values, you can clean, trim, convert text to dates, etc..  With the columns you can split, combine, pivot and unpivot, etc… And with the tables you can truncate, filter, join (known a merge) and union (known as append), etc…

Applied steps and the underlying code

Each time you click on a transformation in the power query editor you generate a step in the query. You can flip between the steps by clicking on the “Applied Steps” pane on the right. This is very useful for understanding what you and other people have done. It’s worth noting that every time you create a step you actually creating code. This is because Power Query is actually a programming language which you can edit (using the formula bar or the advanced editor). You can get by without touching code as the vast majority of operations can be done solely using the GUI, but getting to grips with code will enable you to reduce the number of steps in your query making it more understandable, maintainable, and efficient.

Learning Power Query

Learning Power Query needs an active approach. You need to play with data by clicking on the different buttons in the ribbon and right-clicking on the columns and exploring the options. Try simple problems or try complicated problems that had you stumped for a while in Excel. Whatever you do, you will learn and these skills will pay back big time. In 10 years or so, knowing the Power Query will be like knowing Excel. It will be ubiquitous and necessary.  But if you get in early you can be a superstar.

Entering simple data to play around

Excel is a better option for inserting dummy data for the sole reason that you can easily edit that dummy data later. This is done by typing some data into a worksheet, selecting that data, and converting it into a table (ctrl + t). With that table selected you can click on “

Tip #1: The formula bar

The formula bar is not enabled by default. You can turn it on by clicking on the “View” tab in the ribbon and then enabling “Formula Bar”.

A good case use

For simplicity’s sake, let’s say we have a 1 column table containing 2 rows.
I wish to split the column into 3 columns. The first 2 characters, the next second 2 characters and the next 4 characters and call them Day, Month & Year respectively.

The split option available is not suitable for splitting the column after the second character and the fourth character.

I could split by the column into 2 columns using the option shown in the image above and then repeat the process on the second column to form the second and third columns. Afterward I would rename the columns. This is all, would take 3 steps.

So let’s do the first step:

Using the formula bar we see some functions and their arguments.
{0,2} – 0 refers to the start position (in this case, the very start) and 2 refers the position of the first split
{“Value.1″,”Value.2”} – Refers the new column names
If we change the values to something like below we can get:

So by modifying the code, we can get in done in one step.

The purpose of doing so isn’t about reducing steps out of some sort of obsessiveness but is more about keeping things clean and simple and thus understandable for yourself later on and for others.