This post may contain affiliate links. Please read my disclosure for more info. Power Query Course

Power Query – Basic Transformations

Power Query - Basic Transformations

We’ve looked at a few transformations already in this Power Query series, but now we are going much further and look at the transformations available in the main Power Query menus.  We can’t cover everything in this post; there are just too many options available.  Also, some of the more detailed transformations will be included in future posts in this series.

Microsoft has tried to make the menus contextual; therefore, the same actions can appear in multiple places within the menus.  It’s possible that you will identify other menus to apply the same settings.  As Power Query is continuously updated, it is possible that screenshots may appear slightly different from your version of Excel.  You may also have icons that I don’t, and vice versa.

Without further ado, let’s get started looking at what is available to us.

Home Tab

First, let’s look at the Home tab.

Home Ribbon Complete

Manage Columns

The Manage Columns section contains two icons, each of which includes two further options.

Manage Columns Section

Choose Columns – selects the columns to keep in the preview window.

Go To Column – selects a specific column.  This is not really a transformation as it selects a column without adding any steps to the query.  The same could be achieved by clicking the mouse on a column header.

Remove Columns – removes the selected columns.

Remove Other Columns – removes all the columns except those selected.

If you’ve been reading along carefully, you might be wondering what the difference is between Choose Columns and Remove Other Columns.  Both commands keep the columns which have been selected, so what’s the difference?  In short, nothing!  There is no difference, the M code is exactly the same.

However, from a users thought process, there is a difference.  Look at these two sentences

  • “These are the columns I want to keep.”
  • “I want to remove all columns except these.”

They are subtly different in approach but lead to the same result in Power Query.  So I can appreciate why both exist even if they provide the same result.  This also illustrates how many of the Power Query menus work, they are based on what a user might be trying to achieve.

Remove or Remove Others – does it matter?

Now that I’ve got you thinking a bit.  Here is a question for you… are the following the same?

  • Select the first column and clicking Remove Other Columns
  • Select all columns except the first and clicking Remove Columns

What do you think? Are they the same?

The answer is… No, they are not the same.  Let me explain why.

Let’s assume we have a Table with three columns labeled Column1  Column2 and Column3.

If we were to Select Column1 and remove the other columns.  The M Code looks like this:

= Table.SelectColumns(Source,{"Column1"})

However, if we were to select Column 2 and Column 3 and removed those columns (i.e., leaving Column1 remaining), the M Code looks like this:

= Table.RemoveColumns(Source,{"Column2", "Column3"})

This isn’t just a different line of M code achieving the same result.  Now imagine a new column has been added to the source data.  The new column is called Column4.  We refresh the query as usual.  What’s going to happen?

  • Remove Other Columns will ensure Column1 is the only column remaining (i.e., column4 has also been removed).
  • Remove Columns will remove Column2 and Column3, which means that Column1 and Column4 are remaining.

This concept is important and is found over and over again in the Power Query options.  As you apply steps which have both the Remove / Remove Other versions you need to think what will happen if somebody adds more columns, this ensures you choose the best option for your scenario.

Reduce Rows

The Reduce Rows options remove the number of data rows by keeping or removing rows which meet specific criteria.

Reduce Rows Section

The first three items in both menus lead to another window, which allows us to set the number of rows to keep or remove.

  • Keep Top Rows
  • Keep Bottom Rows
  • Keep Range of Rows
  • Remove Top Rows
  • Remove Bottom Rows
  • Remove Alternate Rows

As an example, the window for Keep Range of Rows looks like this:

Keep Range of Rows window

Or the Remove Alternate Rows looks like this:

Remove Alternate Rows window

The remaining menu items do not lead to another window:

  • Keep Duplicates
  • Keep Errors
  • Remove Duplicates
  • Remove Blank Rows
  • Remove Errors

For these, the steps are automatically added without the need for further input.

Sort

The Sort options are similar to Excel, but there is a subtle difference… how the sort order is applied.

Sort Section

Let’s assume we have some basic data

Sort Example - Basic Data

If we sort by Column1, then separately sort by Column2 what should happen?  In Excel, if we applied two separate sorts, they would end up being sorted by Column2, as that was the last column sorted.  But not so with Power Query.

If you remember, Power Query reduces steps to be as efficient as possible.  If you sort by Column1, then by Column2, it will apply this as a single step and the result will look like this.

Sort Example - After Sort

Notice that the result is sorted firstly by Column1, then by Column2.  Therefore, Power Query sorting is like functionality we find in Excel’s Sort window.

If there is at least one step between the first sort and second sort, Power Query will not combine them as they are separate steps.

Other sections of the Home tab

The Transform section of the Home tab contains duplicates of transformations found within the Transform tab.  Scroll down a bit further and start reading the Transform section to get the full detail of these.

We will not be covering the other icons within the Home tab in this post.  Many of these will be included in detail in future posts.

Transform

Next, let’s turn our attention to the Transform tab.

Transform Ribbon 1

Table

The Table section applies transformations to the entire table, not just the selected columns.

Table Section 1

Group By is a way to summarize the data in various ways.  Clicking the icon opens the Group By window.

Group By Window

Use First Row as Headers makes the first data row the column header.  We often use this when importing data from a named range, or anytime Power Query cannot guess what the column headers should be.

Use Headers as First Row is used when Excel incorrectly assumes the first row of an import should be the column Header.

Transpose works in a similar way to the Excel function, it switches the columns for rows and vice versa.

Reverse Rows puts the rows into the opposite order.

Count Rows isn’t a transformation in my opinion, it drills down to provide a count of the number of rows.

Any Column

The Any Column section contains lots of useful transformations (some of which we will cover in more detail in future posts).

Any Column Section

Data Type – use this option to change the data type.  It has the same result as clicking the data type icon within the column header.

Detect Data Type – Power Query trys to guess the data type by analyzing the data in the column.  Remember, it won’t always get it right, so it’s often better to explicitly select the data type you want.

Rename puts the selected column header text into edit mode, which is the same as double clicking the column header.

Replace Values is very useful; it operates like Excel’s Find & Replace functionality.  Clicking the icon opens the Replace Values window.

Replace Values Window

Replace Errors operates the same as Replace Values but only applies to data containing errors.

Fill Up / Down fills every blank cell in the column with the value above for Fill Down or below for Fill Up.

Pivot Column will Pivot the data based on the selected column.  More on this in a future post.

Unpivot Columns can be used to convert information into a flat file table.  More on this in a future post.

Move is used to move columns left or right across the screen, though dragging with the mouse of often easier.

Convert to List coverts the selected columns to a List of values; this is similar to drilling down into a table (as we have already seen).

Text Column

The tools to transform text are contained in the Text Column section.

Text Column Section

Split Column is useful any time we want to separate part of a string from another string.  Splitting creates additional columns for each split of the string.

There are lots of options for splitting text.  The top three items all open a new window.

  • By Delimiter
  • By Position
  • By Number of Characters

As an example, the Split Column By Delimiter window looks like this:

Split Column by Delimiter window

The bottom four items do not have an additional settings window.

  • By Uppercase to Lowercase
  • By Lowercase to Uppercase
  • By Digit to Non-Digit
  • By Non-Digit to Digit

Format has options to:

  • Change how the text looks (e.g., Uppercase, Lowercase)
  • Trim and clean to remove spaces and non-printable characters
  • Add Prefix and Add Suffix are to add a fixed string to the existing text.

Merge Columns requires two or more columns to be selected.  Clicking the icon opens the Merge Columns window, which contains the additional settings.

Merge Columns Window

Extract contains options to keep part of a string, and discarding others.  Options include:

  • Length (similar to LEN function in Excel)
  • First Characters (similar to the LEFT function in Excel)
  • Last Characters (similar to the RIGHT function in Excel)
  • Range
  • Text Before Delimiter
  • Text After Delimiter
  • Text Between Delimiters

Parse is advanced text filtering for XML and JSON coded text.

Number Column

I’m an accountant.  Therefore I’ve only ever used a few of these.  I’m sure the Trigonometry section is useful for other users, just not for me.

Number Column Section

Statistics drills down into the values to provide statistical information about the data, not something you’re likely to need to do often.

Standard performs calculations on the numbers.  As a basic example, selecting Add opens the Add window.  Entering 10 as the value and clicking OK adds 10 to every number in the column.

Rounding provides options to Round Up, Down, or to a specific number of decimal places.

Information can be used to check if the value in Odd, Even, Positive, Negative, or Zero.

Scientific and Trigonometry – sorry you’re on you’re own with these.  I know I learned these at school, but they have long since left my memory.  Feel free to experiment, what’s the worst that’s going to happen.

Data & Time Column

The Date & Time Column section contains numerous transformations to deal with dates, times and durations.  In Excel, dates are tricky to work with, but within Power Query, we now have plenty of tools to turn our data into something more useable.

Date Time Column

Date contains lots of options for transforming dates.  Some of my favorite transformations from this section are:

  • Start of year (Date)
  • End of year (Date)
  • End of month (Month)
  • Month name (Month)
  • Week of the year (Week)
  • Day of the week (Day)

Basically, from any date, it is possible to generate every categorization conceivable.

Time contains the options to get the hours, minutes, second, etc. from a column formatted as time or date time.

Duration contains options to convert durations into Days, Years, Minutes, Seconds, etc.

Add Column

Transform vs. Add Column tabs

Compare the Transform and Add Column tabs; there are a lot of similar sounding icons.  So, what’s the difference between them?

Add Column Ribbon

Selecting an icon from the Add Column tab will (you’ve guessed it) add a column. Icons on the Transform tab transforms the selected columns, without adding new columns.  To find out what an icon does, read the equivalent icon in the Transform section above.

General

The General section provides more advanced functionality.

General Section

Column from Examples uses Power Query’s logic to identify patterns in the data and apply it to the remaining rows.  The example below demonstrates that by providing the desired outcome for the first two rows, Power Query guesses the pattern and applies this to the remaining rows.

Column By Example

Custom Column enables us to use M code formulas on the data.  We’ll cover some basic versions of these in a future post.

Invoke Custom Function allows us to create our own Power Query functions from existing transformations.

Index Column adds a column with an index number.

Duplicate column creates a duplicate version of the selected column.

Conditional Column uses IF type logic to generate values meeting specific criteria.

Add Conditional Column Window

Filtering

One of the most common types of transformations is filtering a column.   The filter menu is contextual, so the items depend on the column type.  The screenshot below shows the menu for a number column.

Filter Menu

The options are similar to, but not the same as those found in Excel.  For example, in Excel, there is a filter for “Top 10…”, for which an equivalent does not exist in Power Query.  This is not because Power Query lacks functionality, but because there are different ways to achieve the same result through sorting and reducing rows.

Other Menus

Common transformations can be found on other menus too.

Right-click Column Header

Right-clicking on the column header displays a contextual menu (shown below).

Right - click Header menu

All of these are available within the main ribbon, but the right-click menu provides a faster way to access many of the transformations.

Table icon

In the top left of the Table is a small table icon.  Click that icon to reveal the most popular table transformations.

Click Table Top Left

Change data type

As we have seen before, clicking on the icon on the left of the column header displays the menu to change the data type.

Change data-type

Next

That was quite a lot to take in, but don’t worry, you don’t need to remember it all.  You just need to know what Power Query is capable of.  When you need it you can look back at this post again in the future.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

Leave a Reply

Your email address will not be published. Required fields are marked *