You are reading the article Power Query Keyboard Shortcuts To Save Time updated in December 2023 on the website Achiashop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Power Query Keyboard Shortcuts To Save Time
Below is a list of time saving keyboard shortcuts that you can use in Power Query, in both Excel and Power BI.
Watch the video to see me demonstrate how to use them, and download a PDF containing all shortcuts.
Watch the Video
Download Power Query Shortcuts PDF
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
More Keyboard Shortcuts
You may also like to get a copy of my eBook 239 Excel Keyboard Shortcuts
All of these shortcuts work in both Excel and Power BI Desktop, except the shortcut to open the Power Query editor from Excel.
When the Power Query editor is open in both Excel and Power BI Desktop, pressing Alt highlights the keys required to access items on the Ribbon.
General
Open Power Query Editor (Excel Only)
Alt
A
P
N
L
Open Power Query Editor (after adding icon to 1st position on QAT in Excel)
Alt
1
Close Power Query Editor
Alt
F4
Increase Font Size
Ctrl
+
Shift
+
+
Decrease Font Size
Ctrl
+
Shift
+
–
Reset Font Size (don’t use 0 on numeric keypad)
Ctrl
+
0
Navigation
Move around table cells, move between columns, move between rows
↑
↓
←
→
Jump to first column (stay in current row)
Home
Jump to last column (stay in current row)
End
Jump to first cell in first column
Ctrl
+
Home
Jump to last cell in last column
Ctrl
+
End
Jump to first cell in current column (with any cell in column already selected)
Alt
+
Home
Jump to last cell in current column (with any cell in column already selected)
Alt
+
End
Move up/down a page of data in the table
Pg Up
Pg Dn
Go To Column
Ctrl
+
G
then choose the column
Select Columns and Rows
Select current column
Ctrl
+
Space
Select Adjacent Columns
Ctrl
+
Space
to select a column.
to select a column.
Hold Shift then use ← or → to select other columns.
Select Non-Adjacent Columns
Ctrl
+
Space
to select a column.
to select a column.
Hold Ctrl then use ← or → to move to other columns.
Press
Space
to select those other columns
Pressto select those other columns
Select All Columns
Ctrl
+
A
Preview an Entire Row’s Data
Navigate to leftmost column.
Press ← to preview the row’s data.
Use ↑ and ↓ to preview different rows.
Modify Columns
Rename Column
Select the column then
F2
Delete Column(s)
Select one or more columns then
Del
Add Column By Example
Ctrl
+
E
Open Menus
Open Sort and Filter Column Menu
Select a column.
Alt + ↓
Use ↓ ↑ Tab to move around the menu.
Space to select/deselect items. Enter to confirm selections.
Change Column Type Menu
This key is usually found on your keyboard underneath the rightmost Shift key.
The menu displayed is context sensitive – what you see depends on what you have selected.
Table Options Menu
Ctrl
+
Home
Navigate to top cell in first column using
Then ← ↑
Enter or Space to open the menu
Exit Menu or Step Back a Level in Multi-Level Menus
Esc
You're reading Power Query Keyboard Shortcuts To Save Time
Grouped Running Totals In Power Query
In a previous post I looked at creating running totals in Power Query, here I’ll be creating grouped running totals.
I’ll start with a table of data showing values for cities within different countries.
Download the Workbook With Sample Query
The queries in this Excel file can be copied/pasted into the Power BI Desktop Advanced Editor and will work there too.
Enter your email address below to download the workbook with the data and code from this post.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Download the Excel Workbook.
I want to create a running total for each country so that we end up with this where the running total resets when we reach a new country in our list.
Grouped Running Total Custom Function
The M code to create the custom function is very similar to the code for the running totals custom function.
Read More : Power Query Custom Functions
You don’t need to worry about getting your hands dirty with M, you can just call the function as you would any other function in Power Query.
The function is named fxGroupedRunningTotal and takes two parameters named values and grouping, both of these are lists of equal length. The function returns a list which will have the same number of items as the input lists.
In this case the values parameter will be the list of values from our source table, and grouping will be the list of countries.
Here’s how the function works. It uses List.Generate to work through each item in the grouping list, following the rules as described below. It uses two variables GRT which holds the running total, and i which is a counter
Each time a new value is created in GRT, it is added to the list that is the result of the function, GRTList
Set the initial values. GRT takes the first value in the values list. The counter i is 0.
Keep looping while i is less than the number of items in the values list. Remember: lists are indexed from 0.
On each loop : if the current country in the list specified by grouping{[i]} is the same as the next country grouping{[i] +1} then add values{[i] + 1} to GRT else the next grouping (country) is different, so GRT just becomes values{[i] + 1}
Add the value in GRT to GRTList
The try .. otherwise in step 3 is to catch the error generated when [i] + 1 exceeds the number of items in the input lists.
Using the Custom Function in a Query
After loading the source table, the query creates two buffered lists for the values and countries. Buffering the lists allows the query to run much faster as the lists are held in memory and not re-evaluated every time the query needs to refer to an item in the lists.
If you want to group by something else in your data, then change Source[Country] to whatever column you want to use.
Then it’s just a case of calling the function to create the grouped running total, creating a table from the columns in the source table and the output from the function.
The final table loaded into Excel looks like this
How Fast Is This?
As with the running total query, by using buffered lists the query runs in a flash. Even when it’s calculating for 100,000 rows, the query finishes in a couple of seconds.
In the example workbook you can download, I’ve created a table with 100,000 rows of data and a separate query to calculate the grouped running total for that. Try it out for yourself.
Pro Tip – Quickly Create 100,000 Rows of Data
If you need to enter a lot of data, like the 100,000 rows of dummy data for this post, you can do this using the Immediate Window in the VBA editor.
Press ALT+F11 to open the VBA editor. If the Immediate Window is not already visible, press CTRL+G to open it.
Valid VBA commands typed into the Immediate Window get executed after you press the Return/Enter key.
So I can put the value 101 into cells A1 to A10 on the active sheet with
and to fill 100,000 cells with random numbers
Vlookup In Power Query Using List Functions
If you’ve done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this.
Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query
Here I’ll be showing you how to use List Functions in Power Query to do both Exact and Approximate matches.
This approach requires a little more M coding but requires less steps and is my preferred method for doing lookups. But hey, I would think that, I’m a coder 🙂
Using List Functions
The key to doing this is remembering that table columns are lists, and as such you can use List Functions to manipulate the data in them.
Watch the Video
Download Sample Excel Workbook
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
. Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download. Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.
Exact Match
My sample data is a table of sales on different dates for different types of food and drink. This table is named data.
What I want to do is add a column showing the category for each item. These categories are stored in a separate lookup table named categories.
I’ve loaded both tables into PQ and set categories to load as connection only because I don’t need to create a new table from it.
So with both tables loaded into PQ, let’s look at what we need to do.
The Food & Drink query needs to get the Category from the Categories table. By looking up the value in the Food & Drink[Product] column in the Categories[Product] column we can get the row number in Categories[Product] that matches.
Using that row number as an index on the Categories[Category] column will return the Category.
Let’s do this step by step. Start by adding a Custom Column and calling it Category Index.
Using the List.PositionOf function, I want to look up in the Categories[Product] column, the value in this [Product] column.
The new column shows the position of each Product in the Categories table.
Remember that Lists are indexed from 0, so Apple Juice is in Position 0 of the [Product] column in the Categories table.
Atlantic Salmon is position 65 etc.
Now that I have this index number I can use it to lookup the category. Create another Custom Column and call it Category.
What I need to do here is enter a reference to the value in the Categories[Category] column. This is done by specifying the Category Index value inside curly braces after the Table[Column] reference.
Let’s do a litle tidying up, We don’t need the category Index column now so delete it, and reorder the columns.
Before I finish though, I can make things a little neater. I like reducing my steps and code where possible.
You can see in the Added Custom step that the code creates the Category Index column
And the subsequent Added Custom1 step it uses the values from that Category Index column.
You can take the code List.PositionOf( Categories[Product] , [Product] ) from the Added Custom step, and replace [Category Index] in the Added Custom1 step with it.
This condenses the code from two steps into one and you end up with the same result.
As the Added Custom step is no longer needed, delete it. Also delete the Removed Columns step as all that is doing is deleting the Category Index column. But as the query is no longer creating that column, this step is not needed either. I don’t actually need to create the Category Index column at all.
OK so the query is done, load to the sheet and that’s our finished table.
Approximate Match
To do an approximate match I’m going to use this table of sales figures for various sales people and add a new column showing the bonus rate they’ll get for those sales.
The idea being that you multiply the sales amount by the bonus rate to work out the bonus the sales person gets paid.
The bonus rates are stored in a separate table called excitingly, BonusRates.
Make sure the table is sorted in ascending order on the Threshold value. It’s important to note that the first row has $0 threshold and a rate of 0.
The reason for this will become clear as I explain how the lookup query works
The bonus rate is determined by the sales amount. If you sell $10,000 or more, but less than $20,000 then the rate is 0.1
If you sell $20,000 or more but less than $30,000 then the rate is 0.15, etc
Load both tables in Power Query and set the Bonus Rates lookup table to connection only.
What I need to do here is of course look up the sales amounts in the BonusRates table. But this time I’ll use List.Select to create a list of all values in the BonusRates[Threshold] column less than or equal to the Sales Amount
The number of elements in this list will be used as the index to lookup up the bonus rate.
I’ll use the first sales value of $17,606 as an example. There are 2 values in the Threshold column less than or equal to $17,606.
The List.Select function creates a list containing 0 and 10000. Then by counting the items in this list, I can use that number to return the 2nd item in the Rate column which is 0.1, or 10%
Let’s look at the code. Open the Power Query editor and add a Custom Column called BonusRates and add this code
I’ll explain what’s going on here
The variable val contains the Sales value in the current row of this table. Remember that the code written here is run for every row in the Sales column.
List.Select creates a list containing all values in the BonusRates[Threshold] column, that are greater than or equal to the value in val.
The each _ is shorthand for the current item in the BonusRates[Threshold] column. It’s saying compare val with each item in the BonusRates[Threshold] column
The result of this code is a column of lists.
For the Sales value 9238 the BonusRates list contains just a 0, because the minimum sale amount to get a bonus is $10,000.
If there wasn’t a row in the lookup table with 0 threshold then the list for any sales value less than 10000 would be empty. When the query tried to lookup an index using an empty list it would generate an error. Having the 0 Threshold row means that the code will always create a non-empty list and avoid such errors.
Checking the list in row 4 for the Sales value 32455 shows that the list contains 4 items, because this sale amount crosses the $30,000 threshold.
With this new column of lists I can now count the items in each list and lookup the bonus rate for the Sales amounts.
Add another custom column, call it Bonus Rate, with this code
What we need to do is lookup the value from the Rate column in the BonusRates table, and the number of items in the list created in the previous step is the index to that value.
Remember that lists are indexed from 0 so if list has 2 items then the 2nd item is at position 1. Therefore we have to subtract 1 from the count of items in the list
I can now calculate the bonus amount in Power Query, or load this table to Excel and do it there.
Power Query Best Practices For Your Data Model
Power Query is used to prepare each of the tables loaded into the data model. Hence, it’s fundamental that the tables, fields, and measures in the data model should be uncomplicated and user-friendly. In this tutorial, let’s talk about some Power Query best practices for our data model, some of its features, and why we should use the query editor.
Power Query allows users to do very complex stuff. Therefore, it’s always important to follow a couple of best practice rules to keep everything properly organized.
People might usually import their data directly to their data model by using the Get data option.
I highly suggest you do not do that and use the Query Editor first. This means that we should always bring our data to the query editor to clean them first. The reason is because data is never perfect. It would be better to check the data in the Query Editor before adding it to our data model.
One of the most important Power Query best practices that I’d recommend is understanding what a query is.
A query is like a snapshot of our data in its worst form. It doesn’t physically transfer anything into our Power BI model as well.
Since our data tables could be big, we want to query it and don’t create any overload in terms of our Power BI models. Once we get them in the query format, that’s when we do all the cleaning and transforming of those tables. Therefore, it’s crucial to have a good understanding of what a query is versus directly committing data in the data model.
It’s so important in terms of Power Query best practices for model development to organize our queries. This is because we’ll have a lot of queries when we develop more and more inside Power BI. Sometimes, a query could be like a staging table, and eventually might get appended or merged into another table. So, we might get a lot of queries and we need to be able to manage them.
In this example, I organized them on the left hand side using folders. We can also drag and drop our queries to put them in a certain order. The key thing when organizing them is to name them intuitively as well—not only the queries but also the folders that they sit in.
The other Power Query best practice that we need to learn is to know what goes on inside the Advanced Editor and more specifically, with M code.
This is an example of a detailed M code with the dates query. It’s simply a code that will change every time we make a transformation. So, it just lays out all the different details of transformations we’re doing.
For example, let’s remove a column here.
Lastly, I highly suggest users to have an understanding of how we want to structure or optimize tables for Power BI. This is really crucial because at the end of the day, once we get past this query stage, we’re going to commit it to our data model and have to build a data model around it. We’ve got to have the data model in mind as we’re working through this, because this is where we are optimizing your tables for the data model.
So, what is a good shape or what is the most optimal shape for our tables to fit inside our data model? There’s no actual exact answer to that as well because every data situation is unique in a lot of cases.
So, those are my suggested Power Query best practices and some of the main key things that we’re going to cover in the other blog articles. Following these general tips can help you prepare a proper data model which is considered as the heart of a Power BI report solution.
Always keep in mind that it’s really essential to have an understanding of what’s going on inside the Query Editor. From there, we can go and apply what a good and optimized table looks like into our own data scenario and into our own model.
All the best,
Sam
Top 10 Keyboard Shortcuts For Snagit For Mac – Webnots
Snagit is one of the top notch image capturing and video recording apps for Mac. Though you can use the default Screenshot app, processing of images need a quality app like Snagit. When using Snagit, you will be using certain functions like trimming the image frequently. Using keyboard shortcuts for such frequently used functions can save you lots of time.
Snagit Editor for Mac
Top 10 Keyboard Shortcuts for Snagit Mac 1. Command + Shift + O – Grab text from imageDo you know that Snagit has built-in tool for Optical Character Recognition? Yes, you can easily select the text portion on an image and grab the text with a keyboard shortcut. First, select a text on image using “Selection” tool and press “Command + Shift + O” keys. This will open a “Grab Text Results” box and show you the text content for you to copy.
Grab Text from Image
2. Command + Shift + X – Trim imageSometimes your image may have plain white or no background beyond the required portions. Simply press, “Command + Shift + X” keys to trim the image and remove the outside portion.
3. Control + Shift + T – Flatten AllSnagit allows you to create image with multiple layers. This means you can copy and paste one image on another and bring to front or back to adjust. The layering also allows to drag each element on the image like text, stamps, etc. to move independently. When you are done with the complete image, press “Control + Shift + T” to flatten all parts and make it a single image.
4. Command + Shift + Z – Save as to Select Image QualityQuality of an image highly depends on the type of extension you choose to save. PNG is good for high quality images with transparent background. However, you can’t adjust the image size with PNG. On other hand, JPEG allows equal quality with an option to adjust the image quality thus reducing the size. When your image is ready, press “Command + Shift + Z” shortcuts keys to open “Save As” menu.
Trim and Image Quality
Select, “jpg” option from the “Format” dropdown on the bottom of the pop-up. Now you will see a quality slider which you can slide to reduce the quality of the image. We recommend you choosing 60% to 70% quality for the instructional images used on webpages. Since webpages need to load each image as a separate HTTP request, this size reduction will help a lot in improving the page loading speed.
5. Command + , – Preferences to Set Keyboard ShortcutsIf you don’t like any of the existing keyboard shortcuts on Snagit, you can customize it from the preferences section. Press, “Command + ,” and open Snagit preferences section. Go to “Keyboard” tab to change the hot keys for the available shortcuts.
shows the shortcut keys to completely remove the shortcuts or assign the previously used one. If you have messed up the setup, press “Restore Shortcuts” to reset the shortcuts to app default.
6. Hold Shift – Select Square or Circle PortionThe selection tool on the toolbar helps you to grab a portion of the image. You can then copy, move, blur or do other stuffs with the selected portion. You can hold and drag the mouse pointer to select the portion as per the selection tool properties. The problem arises when you want to select a perfect square or circle portion. First select the rectangle option and simply hold the shift key when selecting with mouse. This will help you to precisely select square portion from the image.
Selection Tool Properties
If you want to select a perfect circle portion then change the selection tool to ellipse from the properties.
Bonus Tip: you can choose the background fill as “Auto-Fill”. This will help to retain the background when you drag a portion of an image.
7. Command + 1 – Image Library
When you have hundreds of images processed over time, you may need to navigate to the “Library” to choose previously processed images. Pressing” Command + 1” will simply take you to the “Library” from where you can search and find your old images.
8. Command + Option + T – Toggle Recent Capture TraySnagit will retain some of the previously captured images in the “Capture Tray”. You can quickly select the images from the tray. However, this tray occupy considerable bottom portion of the editor. When you want to increase the editor area, press “Command + Option + T” to hide the tray. You can again press the same keys to show the tray for image selection.
If the sidebar distracts your attention, press “Command + 4” to hide or show the sidebar that shows the properties and effects.
9. Command + ] or [When you have large number of images on the capture tray, it is difficult to find the image you are currently processing. Sometimes, you also need to capture few images in sequence and go to the next or previous image for processing.
Press, “Command + ]” keys to highlight and select the next image.
Similarly, press “Command + [“ keys to highlight and select the previous image.
10. Control + R – Open Favorite Quick StylesSnagit offers hundreds of can quickly collect all different styles like callout, text, arrows, stamps, etc. in a single place and use it quickly. For example, you don’t need to go to stamps and look for your favorite every time. Instead, add the stamp to favorite and use it quickly by using “Control + R” keyboard shortcuts.
Favorite Quick Styles
Final Words
List Of The Best Keyboard Shortcuts For Keynote On Mac
If there’s an app that you’ll want keyboard shortcuts for on Mac, it’s Keynote. You can navigate your presentation and any videos in it, move around views as you’re creating a slideshow, or simply control the Keynote window.
The Keynote windowControl the Keynote window by minimizing, hiding, or closing it using these handy keyboard shortcuts:
Minimize the window: Command + M
Minimize all windows: Option + Command + M
Hide Keynote: Command + H
Hide all other app windows: Option + Command + H
Enter full-screen mode: Control + Command + F
Zoom out: Command + < (left angle bracket)
Zoom to selection: Shift + Command + 0 (zero)
Zoom to fit: Option + Shift + Command + 0 (zero)
Return to normal size: Command + 0 (zero)
Open Keynote preferences: Command + , (comma)
Open the Pages User Guide: Shift + Command + ? (question mark)
Close the window: Command + W
Close all windows: Option + Command + W
Quit Keynote: Command + Q
Quit Keynote with windows open: Option + Command + Q
Keyboard shortcuts for playing a presentation using presenter modeWhen you’re ready to play your presentation using presenter mode, have this list of keyboard shortcuts nearby to move smoothly through the slideshow:
Play your presentation: Option + Command + P
Pause your presentation: F
Pause your presentation with a black screen: B
Pause your presentation with a white screen: W
Go to the first slide: Home or Fn + Up arrow
Go to the last slide: End or Fn + Down arrow
Go to the next slide: Right arrow or Down arrow
Go to the next slide without animation: Shift + Right arrow
Go to the previous slide: Left arrow or Up arrow
Go through previously viewed slides: Z
Show the slide number: S
Show or hide the pointer: C
Show or hide the Presenter Notes: Shift + Command + P
Scroll up the Presenter Notes: U
Scroll down the Presenter Notes: D
Increase Presenter Notes font size: Command + Plus sign (+)
Increase Presenter Notes font size: Command + Hyphen (-)
Switch the primary and presenter displays: X
Reset the timer: R
Hide the presentation and move to the last app used: H
Quit presentation mode: Escape or Q
Show or hide keyboard shortcuts: ? (question mark) or / (forward slash)
Keyboard shortcuts for controlling a video in your presentationIf you have a video embedded in your presentation, you can control it with keyboard shortcuts too:
Play your video: Space bar
Pause or resume playing the video: K
Rewind the video by frame when paused: J
Fast forward the video by frame when paused: L
Move to the beginning of the video: I (capital letter “i”)
Move to the end of the video: O (capital letter “o”)
Keyboard shortcuts when using Navigator viewFor moving through your presentation as you’re creating it, use these shortcuts in Navigator view:
Select multiple slides: Shift + Drag through the slides
Indent selected slides right: Tab
Move indented slides left: Shift + Tab
Add a new slide at the same level as the selected slide: Return or Shift + Command + N
Duplicate a selected slide: Command + D
Delete a selected slide: Delete
Go to the next slide: Down arrow
Go to the previous slide: Up arrow
Skip a slide in your presentation or show a slide you’re skipping: Shift + Command + H
Expand a group of slides: Right arrow
Collapse a group of slides: Left arrow
Keyboard shortcuts when using Light Table viewFor moving through your presentation as you’re creating it, use these shortcuts in Light Table view:
Select the first slide: Command + Up arrow
Select the last slide: Command + Down arrow
Expand your selection to the next slide: Shift + Right arrow
Expand your selection to the previous slide: Shift + Left arrow
Expand your selection to the first slide: Shift + Command + Up arrow
Expand your selection to the last slide: Shift + Command + Down arrow
Go to the next slide: Right arrow
Go to the previous slide: Left arrow
Using keyboard shortcuts for Keynote on Mac can help you not only create your slideshow faster but navigate while you play it easier.
For more, browse through our Keyboard Shortcuts section for controlling other Mac apps.
Check out next:
Update the detailed information about Power Query Keyboard Shortcuts To Save Time on the Achiashop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!