One of the coolest features of Power Query it’s something that we colloquially call ‘the gear icon’ which is an icon that appears to the right of some steps. Not all steps, but to most of the steps that are automatically created when you click on a button of the Power Query UI.
When using Power Query, you might’ve come across a step that reads “Navigation” and is usually automatically created for you by Power Query, but…what does it do?
Well, if we look at the code that it was created for the Navigation step, it reads:
= Source{[Item=”SalesTable”,Kind=”Table”]}[Data]
It has some curly brackets mixed with some square brackets and some nomenclature that seems pretty strange at first. This is one of the ways that Power Query automatically creates a navigation step, but there are other ways to achieve the same result and depending on the situation Power Query might create a different code.
This is the main reason why I’m writing this post. To document what are the ways that Power Query has in order to Navigate to a specific Column, Row or even a specific Cell.
With Power Query inside of Excel or Power BI, you can replace values by using the ‘Replace values’ button, but there are other approaches when you need to replace multiples values at the same time like the following ones:
The reality is that there are multiple ways to do it, but it often requires creating another Translation table in order to perform the needed replacement or translation.
As any other programming language, Power Query handles errors in its own unique way and the goal of this blog post is to give you a few hints on how to audit the errors or warnings that Power Query might throw your way.
Ken and myself just released a new Introductory video course on Udemy!
This course is basically an introduction to the potential of Power Query and the videos are taken directly from our full Power Query Academy, so you can rest assured that they are of high quality and high production value.
