Okay, be prepared to unlock the hidden
gems of Excel's Power Query Editor. If you use Power Query in Excel (which I hope
you are if you're watching my channel), then you're about to discover some tricks
and hacks that even the pros might have overlooked. They're simple, but they
will make your life a lot easier. Let me know which one is your favorite.
Mine is Tip Number Three and Number Six. Tip Number One: Power Query Editor Shortcut.
So, some months ago, we got a new shortcut key combination that opens up the Power Query
Editor, and you might have missed it. That shortcut key combination is Alt + F12. So, you can be anywhere
in your workbook, and when you press Alt + F12, you open up the Power Query Editor. If you have
some queries already there, you get to see all of them here. Now, if you happen to be on the result
of your query, so let's say the result of this query is a table, and I want to go and edit this
specific query, I can use Alt + F12 on here, and I'm taken directly to this query. If you have a
blank workbook open and you press Alt + F12, you are going to bring up the empty Power Query Editor,
and then you can go ahead and import your files. Tip Number Two: Power Query Quick Access
Toolbar. Did you know that Power Query has a Quick Access Toolbar you can use and it's right
here? So, if there are features that you use often and they're in different places, you can
bring them all together to this QAT bar. So, for example, let's say I use "Column Quality"
often. I'm going to right-mouse click "Add to Quick Access Toolbar". Let's say I use the "Choose
Columns" -> "Go To Column" feature often, right-mouse click "Add to Quick Access Toolbar". If I
insert new queries or blank queries, new source, other sources, right-mouse click "Add to Quick
Access Toolbar". Now, if you find that this is getting too long, you can show it below the
ribbon. Now, if I want to add a blank query, it's as easy as going here and selecting "Blank
Query". If I want to activate "Column Quality", I just have to toggle this on and off as I need
to, jump to a column, select this, and jump to the column of my choice. So, basically, you get to
add the features you use most often to the Quick Access Toolbar to minimize jumping around the
ribbon and looking for stuff. How cool is that? Okay, so quick interruption to share some exciting
news. I've just released a brand new Power Query Automation course. It's called "Automate with
Power Query: Recipes to Solve Business Data Challenges". In the course, I cover a collection
of Power Query Excel recipes to help you automate common data problems, especially for business
users. This course will be valuable for you if you already use Power Query at work but don't feel
confident enough. Right? You might find yourself dreading getting new Power Query assignments
because the data you're dealing with lately is super messy, and you have no idea how to start
to clean it. Or you might end up figuring it out, but your query ends up breaking a few days later.
Right? Here's the thing: A good doctor doesn't come out of medical school; they come out of
hospitals and clinics. They become great by being exposed to many different problems. That's
what this course does. It was created based on the struggles of Power Query users. So as you're going
through each lecture, it's like you're visiting one patient after another and getting better
and better as you progress through the course. But that's not all. In my courses, we always
look at the big picture. So, Power Query is just one automation tool. It's a great one, but
there are others that you might be missing out on just because you don't know of them or you haven't
dared explore them. This course is going to shine a light on these other tools as well. So, even
though it's called "Automate with Power Query", there is a supporting cast of Power Automate, VBA,
Office Scripts, and even Chat GPT. Understanding your automation options and the pros and cons of
these tools is going to help you stay on top of the curve. And if you're sitting in a business
meeting talking about automation, you are going to be able to confidently discuss different
automation possibilities. Link to the course is in the description. I'd be delighted to see inside
the course. Now, let's get back to the video. Next tip: Don't type the dot. What this means
is that if you're in the Power Query Editor and you're typing in an M function, let's say
"Table.ColumnNames", and I don't see it here, so I'm going to continue typing "table.columnnames". Now I see it, right? You can use the arrow keys to move around, but it's on top, so
I'm going to press Tab. Look what happens. I get "tabletable.columnnames", and then I have
to go and remove that first "table" and then continue with my function. This annoyed me for
the longest time until I realized I should never type the dot. I should just continue typing. So if
I want "table.contains" and I don't see it here, I don't need to type the dot. I just continue
typing until I see it, and then I press Tab, and everything works fine. Now, another tip
while we're here: If you ever want to get help on this function before you start typing it
out and filling in what's inside the brackets, just press Enter, and you are going
to get the help directly in this view. Tip Number Four: Shortcut to Zoom. You know how
when you're in Excel and you want to zoom in, you can hold down the Control key and
then just turn the mouse wheel to zoom in, and then go the other way on the mouse wheel
to zoom out? Well, you can't do that in Power Query. In the Query Editor, if you try
to use the mouse wheel back and forth, holding down Control, nothing happens. What
you need to do instead is hold Ctrl + Shift and then press the plus key to zoom
in and the minus key to zoom out. Next tip: Automatically Sort Your Column
Headers. So let's say the current order of my column headers is: 1 - Customer Number, and
then it's 5 - First Name, 6 - Last Name, and so on. I want these sorted so that 1 is first, then
I have 2 - Country, then 3 - City, and so on. Now, this is a small dataset. I could easily drag and
drop these, but if you have lots of different columns, it's not going to be so easy. You can
do it automatically by going to "Choose Columns" then adjust the order here. Instead of the natural
order, select "Name". This is going to source the columns alphabetically. Now, when you click on OK,
you have your column headers sorted immediately. Next tip: Select a Folder Icon to Grab
Everything. So let's say you are importing data from this Excel file, and the file has
multiple sheets. Now, you can select a specific sheet to just import the data from that sheet
or click "Select Multiple Items" and select the sheets that you want to include. But what
if you want to include everything and also any future sheets that are created? Well, what you
need to do is uncheck "Select Multiple Items" and instead select the folder icon. This will
include everything that's in the file. Now, in the next step, you can go ahead and
add filters if you don't want everything, or you can go ahead and combine all
the information together. So, this way, you're dynamically appending the information in
the files. Right? So, it's up to you which way you want to go for. But some people forget that
the folder is actually selectable, and that's the most dynamic way of grabbing all the information
in that file. The same concept works on PDF files. Next tip: Add the Table Name Box to Your Quick
Access Toolbar. So in Excel, we have our Quick Access Toolbar by default up here, and we
can add any features we use often to it. So, if I use "Refresh All" a lot, I can right-mouse
click and add to Quick Access Toolbar. If I import a lot of text or CSV files, I can also add it
to my QAT bar. If this starts to get too long, I can right-mouse click and show it below the
ribbon. Now, a cool feature you can add to it is the ability to update table names. The fastest
way of doing this is go to an existing table, go to Table Design, go to Table Name, right-mouse
click, and add to QAT bar. Now we have it right here, so no matter which tab we're in, we
can always see the table name and we can adjust it if we need to. Now, you might want
to reorganize this. Just right-mouse click, customize the Quick Access Toolbar. I'm
just going to bring up the "Change Table Name" all the way to the top and add a separator
below it. Now, I always have it in the front. Tip Number Eight: Copy and Paste Queries. Did
you know that you can easily copy a query from one file and paste it into another file? So,
for example, let's say I want to use this "Current Day" query in another file. I'm going to
right-mouse click and copy it, open up my other file, go to "Data", "Queries and Connections",
go to the pane, right-mouse click, and paste it in. Now, that query, together with all the steps,
gets copied. The good thing is that if you copy over a query that has other related queries, they
also get copied over. So, for example, the "Sales" query in this case is dependent on the other two
queries. If I copy this, go to my other file, right-mouse click, and paste, the other related
queries come over as well. How cool is that? Next tip: Add Slicers to Your Tables. So, Power
Query results can end up being really long, and you might want to add a more user-friendly
way of filtering your table results. You can use slicers for that. I'm just going to push this
table down, then just click somewhere inside the table to get "Table Design" pop up in the
ribbon, and then go and insert a slicer. You can pick the column you want to add a filter for or
a slicer for. I'm going to go with "Department", and OK. You can also have multiple slicers. For
me, in this case, just one slicer set is fine. These are my different departments. They look
like buttons, so I can click them and filter my table. Now, you probably want to arrange this in a
nicer way, so I'm going to go with a design that's similar to my table, I just need columns for the the slicer to fit in a single row. You can also remove the header under Slicer Settings if you want. Then you just have to arrange it somewhere around your data set. Now, you have an easy way of filtering your table results. Last tip is to Adjust your Refresh Settings. So, this is my Power Query results and I want to make
sure that my table is updated every 20 minutes. I can do that by going to "Data", "Refresh All", "Connection Properties", and
adjusting the settings here. So, under Refresh Control, for Last Refresh we can say refresh every, let's say 20 minutes. You can also place a checkmark to refresh data when opening the file. This makes sure that whenever you start working on this file,
the data is refreshed and you're working on the latest data set. Okay, so I hope you enjoy this set of Power Query tips and tricks.
Don't forget to let me know which one is your favorite before you leave. Right? Also, if you're interested to build your Power Query skills and become better and learn more about other automation tools, make sure to check out my
new Automate with Power Query course. Link is below. Thank you for watching and I'll catch you next time.
