Power Query: Key Facts

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!