In this article, I will be covering 5 random tricks which I find very useful for all kinds of queries. This tricks are:
- IF Function for Steps
- IF Error
- “Side” Steps
- 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 🙂
Setting up parameters can save lots of time when:
- You want to “send” filter value into multiple queries.
- 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:
- You can use try … otherwise … in a custom column.
- You can try … otherwise … on step level as in the previous example with IF Function for Steps.
Example for #2
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.
Add this piece of the code (row 4 – #”XX – Table Row Count”):
You see? You now know how many rows there are in the table while the query continues with “Renamed Columns” step.
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
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.
This tricks let you optimize your code for better performance and orientation. I strongly recommend you at least try them.