Power Query: Key Facts
14 April 2021
Welcome to our Power Query blog. This week, I look at some shortcuts to use in Power BI Transform Data.
I am taking a trip to Power BI this week to investigate some of the key shortcuts I can take in the Advanced Editor. It’s not intended to be an exhaustive list, but just a selection of those that I find useful.
For instance, I always have Intellisense activated in ‘Options’:
The first two shortcuts are to do with creating comments in the Advanced Editor. I have accessed the M code for a query to create a calendar table:
If I hold down the CTRL key and press the forward slash key (“/”), I can toggle the line I am on to include a comment, or “uncomment” it. If I select a portion of the page and then use this shortcut, all the selected lines of code are affected, viz.
Alternatively, I can hold down ALT and SHIFT and press “A” to comment out a section of selected text:
If I want to enter text in multiple places at the same time, I can hold down ALT and then left-click where I want to enter the text.
There are also some display options I can pick when I have many lines of code. First, I will turn on the line numbers so I can see what I am doing:
I can then see the lines.
I can also choose to see a ‘mini map’ of the code to help me move around.
If I have a line of code that I want to move up or down in relation to the rest of the code, I can hold ALT and press the up or down arrows.
and then I can move it down, for example:
Back in the Query Editor view, if I have many columns, I can hold CTRL and press “G” to choose a column to go to (that’s a bit like its Excel counterpart, CTRL + G, which calls the ‘Go To’ dialog):
I can also move through my columns by holding down ALT and using the forward and backwards arrows. However, holding ALT and using the down arrow gives me a different result: the Sort / Filter pane is revealed:
Pressing CTRL and the down arrow gives me a different dropdown, where I can change the data type:
Back in the Advanced Editor, there are a few more shortcuts I can use to manipulate the lines of M code:
- Pressing TAB to indent is straightforward, but I can also use CTRL and close square bracket (“]”). The difference is that the cursor moves with the indent if I keep using TAB, whereas CTRL ] leaves the cursor at the first indent and moves the text
- I can remove whole lines by clicking on the line I wish to remove (it’s not necessary to select the whole line) and then holding CTRL and SHIFT down while I press “K”. I must be careful with this one!
- Finally, pressing CTRL and “Z” will undo changes, which is very handy when I’ve carefully entered many lines of M and then realised I have done something daft!
- CTRL and “X” will redo the change if I go too far.
I hope you found this summary of my favourite shortcuts so far found useful!
Come back next time for more ways to use Power Query!