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.
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.
Posts Related:
- MAPA DE CONTRIBUIÇÃO - PLANILHA CADASTRO DE MEMBROS E CONTROLE DE DÍZIMOS
- Excel Today - Power BI's New Interface Office
- Excel Today - Microsoft Planner Office
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.
