Today, I will cover the remaining part of the Power Query User Interface: Queries, Query Settings and Status Bar. Let’s start.
Queries
In the world of data Query is basically a request for information. We know already that Power Query allows to connect to variety of data sources, therefore Queries pane should contain all those data requests in form of the tables that will be loaded to Semantic Model. If that was your guess, you are almost right. In Power Query, Queries Pane contains not only the actual queries, buy also other objects that help to evaluate them, like functions and parameters. Moreover, data related queries can have multiple forms. Of course, only Table structure will be loaded into your model, you may have a data query in form of a list, record or even a scalar value. These data structures could be used by other queries to build the final result.
Last sentence is quite important, as it kind of suggests that you could utilize one query inside another one. That is of course the case. This means that you can put more effort into design process of your Queries, and make sure that you don’t include the same business logic in multiple queries. This is technique is called Staging, and it’s very common in ETL processes.
Your queries (regardless of the type) can be either data source related, or manual. Power Query allows you to provide some information manually: input parameters, mappings, filtering conditions, you name it. Especially for mapping tables you could say that they should be rather maintained somewhere else, so they could be re-used in other reports, but still, the option is there for you to be used wisely.
You can decide if your query will be loaded to Semantic Model. As you probably already know, the size of the model is something you should be careful about, therefore, especially for all the Staged Queries it is important not to load them unnecessarily. To manipulate this setting, you can toggle on/off Enable Load, or do it inside Query Properties:Figure 1. Query Properties – Enable Load.In the same screenshot you see another setting which is Include in report refresh. If you connect to the data source where data is no longer updated (it is very common for archived data), there is no point spending useful resources on refreshing it with the remaining data. This is yet another option for you to save some money on running your Power BI resources.
As your Query list grows, it is important to organize them a bit better and make it easier to understand it by others. You can select queries and use Move to Group option to put them inside of the logical container.
Copy option is very useful, as it allows you to quickly move entire Query from one file to another Power BI file. However, at this point of time, where re-usability needs already occurred, you might as well copy your query from desktop application into Dataflow, and make sure that others in your team/organization can use it as well. Great thing about Copy is that in case your Query leverages other Staged Queries or Parameters, they will be copied along the selected query.
This will be the topic for later, as it is a bit more advanced, but you might create your own Function that contains the specific transformation to be re-used on multiple columns/tables/etc. Not to mention that you can maintain your own Function Library with the team/organization, that will help others to make great use of your knowledge.
During query design process, if you want to try other approach on existing query, it is better to Duplicate it and test on the copy and risking you will lose your entire work progress.
Duplicate option is not really recommended if you want to have a couple of Queries using the same data source and (at least to some degree) the same transformation steps. Instead of duplicating the logic it is better to Stage the Query by including only common set of transformation and create new query that will use the staged one. To do it, use the Reference option. It will create a new query with parent query used as a data source.
Last but not least, if you started already your M code journey, you might prefer to work with Advanced Editor. This is the same that you can find in View tab in the Ribbon.
As you can see, this small pane has a lot to offer. Don’t worry if it seems too much now, it is just to help you navigate, but I highly recommend testing all these options by yourself. For now, let’s move to the next area of UI – Query Settings.
Query Settings
Let’s not talk about whether the naming here is fortunate or not. Most important thing is that you will find inside. Properties you already know. There is one property that is easy to update which is Name. To update the rest, you must click on All Properties:
Figure 2. Query Settings.
Now, the most important part of Query Settings, and one of the best features of Power Query – Applied Steps. Power Query is a self-documenting tool. All the transformations that you apply in your Query are recorded as a new step in Applied Steps. Each step has its name generated automatically, and reflecting the type of operation that was performed. If Power Query finds already a step on the list with the same operation name, it will add and Index number as you can see in the screenshot (Added Custom, Added Custom1). Generic steps (like Source, Navigation), or the steps created with UI will have the cog wheel next to the name, that allow you to open dialog window and modify apply step. You can always modify the transformation directly in the Formula Bar. If you went through previous articles in Power Query UI series, I already mentioned, and I will keep repeating that you should enable Formula Bar. Once you become slightly familiar with the code, a new world of capabilities will open up before you. You can move the steps up or down the list. Code wise it will not always make sense, but it is technically possible. Steps can also be removed from the list if you decide that step is not needed or could be consolidated with another step.
Be careful!When you want to remove the last step in the list, Power Query will not ask you for confirmation! If you worked on heavy transformation, there is a risk that you will remove it by accident and there is no "undo" button.
As always, there is a plenty of Steps settings available. All you have to do is right click on a step to see the list of options:
Figure 3. Applied Steps Settings.
Let’s go quickly through this list:
Edit Settings – related to what was mentioned before, for the Generic Steps and transformations done through UI, you can edit the settings here or using the cog wheel next to the step name.
Rename – Power Query gives generic step names. As you could see in the screenshot, we already have two “Added Column” steps. In case we have 5 or more, it will be difficult to navigate and find the step you are looking for. That’s why it is important to provide more meaningful name. It will help your future self and others.
Delete – each step can be removed any time. Be careful, as your next steps may stop working if they rely on the removed step.
Delete Until End – very useful feature. If you have a lot of steps in the list, maybe you are testing different approaches, and you decide that half of the steps are not needed, instead of deleting step by step you can select one and use this option to remove all the steps below as well.
Insert Step After – creates a blank step at specified location in the list. This step can be modified through code only.
Move before/after – as mentioned before, you could move the steps around with drag and drop or use these options. However, drag and drop probably is more convenient.
Extract Previous – similar option to Duplicate Query. It will create a copy of your Query. The difference is that the copy will not contain all the steps as original Query. If you select a specific step, Copy will contain all the steps before the one you selected.
View Native Query – very very important feature. In case your data source supports Query Folding – capability of pushing transformation steps to the data source – you may view the SQL code generated by your transformations. If this option is active, it’s indicating that Query Folding works fine. If it’s greyed out it most probably doesn’t work, but this way around you can’t be sure and more checks are needed.
Diagnose – this will be big topic on its own. Diagnose you could see available also in the Ribbon, in Tools tab. It allows to investigate which steps are slow and why.
Properties – transformation steps have two properties:
Name – not that relevant as we could rename the Query already at the of tope list.
Description – very important in case you have complex transformations, and you feel that some of the steps should be wrapped in a context, that will help to understand what you had in mind. Description is visible in Advanced Editor as a comment before the step, or as a hint next to the step name:
Figure 4. Transformation Step Description.
Again, a lot of options available and maybe not all of them make sense to you yet. Play with it around and find your own use cases for them.
Status Bar
This is a small section at the bottom of your screen, holding useful information. For example, at the bottom-right corner it shows the Preview Status. Either when it was successfully loaded last time, or it will show you the progress of loading data while applying heavy transformation steps like Sorting or Grouping:
Figure 5. Status Bar – Preview Status.
In the bottom-left corner, you can see the summary of your data preview, number of columns and number of rows. However, number of rows is limited by the size of data sample loaded to your Preview. That’s why, it will show correct value only for the number of rows below 1000, which is a bit unfortunate but understandable. Next to this information we have something that looks like another message, mentioning that Column profiling is based on top 1000 rows. This is not just a message; you can click on it and switch it to Column profiling based on entire dataset.
Figure 6. Status Bar – Preview Statistics.
You may recall from previous articles, we could enable Column Profile in the View tab of the Ribbon. Here we also see the term Column Profiling in the status bar, but this is actually related to Column quality, Column distribution and Column profile available in the View tab. Therefore, if you switch the logic for column profiling, Power Query will be forced to load entire data in order to populate column statistics. This can be unnecessarily heavy task that can impact the data source and make your interaction with Power Query slower.
This is one of the areas that is significantly better in online experience, which is Dataflows. It allows you to switch the views, indicates whether there are errors in your transformation (as long as they are detected within the preview), or maybe not all columns are properly formatted. Power BI Team promised to make these experiences aligned, but for now, we must deal with different UI in Desktop version of Power BI.
Conclusion
This was the last part of the Introduction to Power Query UI series. I didn’t cover much on the functional side, rather just to help you understand the tool a bit better before you start using it. It’s just the starting point for us to talk about individual features in details and, finally, start the coding journey together.
As always, thank you for staying till the end and see you in next article 🙂
Pawel Wrona
Lead author and founder of the blog | Works as a Power BI Architect in global company | Passionate about Power BI and Microsoft Tech
Power BI Settings are very often overlooked by beginners. Many of them have very significant implications. Let’s uncover the secrets of Auto Date-Time feature,
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.AgreeDon't agreePrivacy policy
You can revoke your consent any time using the Revoke consent button.Revoke consent
