5 No Longer Secret Power Query Tricks

In this article, I will be covering 5 random tricks which I find very useful for all kinds of queries. This tricks are:

  1. Parameters
  2. IF Function for Steps
  3. IF Error
  4. “Side” Steps
  5. Adding Comments into the Code

Note that this article is for advanced users. Manual edits of the code are needed but you can make your first step to becoming advanced user by reading & trying the tricks in the article đŸ™‚

1. Parameters

Setting up parameters can save lots of time when:

  1. You want to “send” filter value into multiple queries.
  2. You want to e. g. add a column with a static value into multiple queries from one place.

The main benefit of parameters is that you can manage then from one place and that you can re-use them in multiple queries. Parameters can be text, numbers, lists, true/false – pretty much all data types in Power Query.

This video shows how you can setup a simple filter parameter:

This video shows how you can setup simple Label parameter and then use it in Label column:

2. IF Function for Steps

Let’s assume you want to apply an ad group filter via parameter only if our AdGroupFilter parameter is not empty. How would you do that? Watch the video below:

So when would you use such trick? You may have a query which is simply too complicated to run during development phase and you want to test your steps only on 1 row (in our example, specific ad group). So you setup one conditional step which uses e. g. a parameter as input. In this example, I was using the parameter as condition but the condition does not have to always be a parameter. Condition can be e. g. number of columns in the table. (…if List.Count(Table.ColumnNames(SomeTable)>X then … else …)

3. IF Error

When working with standard Excel, users are frequently incorporating IFERROR() function on the top of their nested formulas in order to decide what happens when formula returns an error. Example is IFERROR(6/0, “Watch out, you are trying to divide by zero”). How would you incorporate similar thing into your queries?

In Power Query, the syntax is try ….  otherwise…

There are basically 2 use cases:

  1. You can use try … otherwise … in a custom column.
  2. You can try … otherwise … on step level as in the previous example with IF Function for Steps.

Example for #2

So in the example, I am trying to reorder the columns into this order: Campaign – Ad group – Keyword. Since the Keyword column is not in my table, the step would return an error:

In order to be ready for this error, I will deploy try … otherwise …:

The Table.Reorder() function would return an error (since Keyword column is not in the table). However, since I have inserted try … otherwise … into the formula, “otherwise” part of the formula then references #”Input Table” and no error is thrown.

4. “Side” Steps

Imagine you have a complicated query and you want to know how many rows your table has at certain step. How would add a row count step? It’s time for opening Advanced Editor.

You want to add the counting step between “Changed Type” and “Renamed Columns”:

Open the Advanced Editor:

Add this piece of the code (row 4 – #”XX – Table Row Count”):


The result is:

You see? You now know how many rows there are in the table while the query continues with “Renamed Columns” step.

Now the same screen from Advanced Editor but highlighted:

So the red piece is the new step. Notice it uses #”Changed Type” as source table from the 2nd step. The blue piece is still referencing #”Changed Type” as well, so you can still rename the columns in the last step. This situation creates an important finding: While lot of people think they steps need to follow each other, it is not the case. Usually, the steps are automatically referencing “previous steps” = “a result table” of that step, especially when you use buttons in the UI. In my example, the table #”Changed Type” is used as source in 2 future steps – in the “XX – Table Row Count” and in #”Renamed Columns”.  While I use the “step” word, it should be rather called “transformed table”.

5. Adding Comments into the Code

This an easy one. Comments have no impact on the functionality of the queries, they just let you stay organized. You simply start with // and write something directly into the formula area:

I strongly recommend to comment important steps in the query so you know why you did what you did e.g. 2 months later. Comments also help you co-workers to get sense of what’s going on faster when they open your files.

Summary

This tricks let you optimize your code for better performance and orientation. I strongly recommend you at least try them.

2 thoughts on “5 No Longer Secret Power Query Tricks

  1. Good stuff! Thanks for posting this.

    I was aware of most of these, but that try… otherwise… was brand new to me, so thank you very much.

Leave a Reply

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