One of the coolest features of Power Query it’s something that we colloquially call ‘the gear icon’ which is an icon that appears to the right of some steps. Not all steps, but to most of the steps that are automatically created when you click on a button of the Power Query UI.
The goal of the Gear Icon
The purpose of this icon is to simply reconfigure or modify argument/s used in the step in question.
So, for example a query that looks like the one on the next picture:
if we click on the first step called ‘Source’, we can get the following window:
if we click on the step called ‘Removed Rows’ we would get the following window:
so it’s all contextual to what was done in that specific step. So for the next image, we click on the ‘Filtered Rows’ step and we see that the window looks different:
Why isn’t the gear icon everywhere?
That’s a good question! and is something that we might need to ask the Microsoft folks directly. We don’t have an official answer from them about why some steps might not have a gear icon, but one could argue that some steps are too complex to display a window interface that provides the required added-value, for example, if you had a step that was actually a function or some other complex scenario that uses nested Power Query operations, then it would be quite difficult to provide the right window without being too complex or difficult to use.
The usual rule is that if you create a step through the UI and you get a configuration window (like the ones shown in the images above), then you’ll most likely see a gear icon, but if you don’t see a configuration window then you might not get the icon.
Bonus: Recommended way to change Data Source
We usually try to change the data source by going to the Source step of every query that we have in our files. I highly encourage you to stop doing that right this moment. There’s a better way to do this and it’s by going through the ‘Data Source Settings’ and changing the data source through that window.
For example, I have this report that I created in my old laptop and now that I’m on a desktop PC I need to change the file path that was used in my old laptop:
I could definetely go through the Source step and change the file path there, BUT, if I had multiple queries referencing a folder or perhaps multiple queries referencing the same database then that could be a REALLY tedious task.
Instead, go ahead and click on the Data Source Settings button on the top left of the Home tab.
The whole process will look like this:
- Click on the Data Source Settings button
- In the Data Source Settings window, select the data source from the list and then click on the Change Source… button
- A new window should pop up which should allow you to change the File Path or simply any of the arguments of your Data Source function. If it was a database, then you’d be able to change the server or database name as needed
and voilá! with just 3 steps, you were able to completely change the data source from all of your queries that used that same data source. You no longer need to go through every single query to check every single Source step. Just do this and you’re good to go!
This is EXTREMELY helpful for when you’re using the New Combine Files that was released last year for the Excel 2016 version of Power Query.
To learn more cool techniques and more advanced scenarios with the M language, go ahead and enroll to the FREE trial of the Power Query Academy where I talk more in detail about these type of techniques.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.