In previous article I started to talk about Power Query UI. Welcome in second article of the short series, where I will try to help you start working with this great tool.
Ribbon
If you think about that one component that is common for all Office 365 applications, I am pretty sure that’s the ribbon. In Power Query it provides you access to a lot of Power Query functions, that allow you to transform the data with just few clicks. Let’s have a quick look at the ribbon before I start to talk about all the available tabs:
Figure 1. Power Query Ribbon.
Before I discuss the actual Ribbon, I want to briefly mention what hides under File. You will find there exactly the same Options and Settings section as it’s available in Power BI. There are of course Power Query settings there, but they will be covered in separate article. You can also save file from there, and important information is that you can save Work In Progress files as well, meaning that you don’t need to apply transformations before the file is saved and closed. However, what is the most Power Query specific here is the options:
Close & Apply – once you are done with all the transformation, and you want to load your data into the model -> use this option.
Close – if you don’t want to load your data yet (as it will “block” Power BI for the time of load), you can close the window without applying the changes.
Apply – If you are not done yet with Power Query but you want to apply your changes in batches from time to time -> you can use this option.
As you can see, nothing complicated here. It’s just important to remember, that if there are not applied changes, Power BI will remind you about when you are going to close your file. Be careful not to lose your work progress.
Home
Home Tab in ribbon is… a bit of everything. There is also a lot of redundancy when it comes to Home features, as they are available also in other Tabs.
As it usually happens, UI features in Ribbon are organized not only in Tabs, but also in Sections within Tabs. For Home tab we have following sections:
Close – only one option here as the rest is available in File.
New Query – here you will find Data Sources, Recent Sources and Enter Data feature, that allows to manually create a data table.
Data Sources – again, only one feature here. It’s Data Source Settings, and this is exactly the same thing we find in Options and Settings. Still, it is useful to make it available here as well.
Parameters – very important feature, as they unlock a lot of options to organize our Queries a bit better. They will be covered in separate article. There is also a way to work with Parameters in Queries list, but you can consider this one as a main component and it doesn’t appear in other tabs in Ribbon.
Query – although it’s a unique section in the Ribbon, we can perform all actions from this section when working directly in Queries list. However, it’s much easier to discover them for Power Query beginners. These options allow you to perform an action on a Query that is selected in Queries list below.
Refresh Preview / Refresh All – while Refresh Preview is quite obvious, it will refresh the data preview for a currently selected query. However, Refresh All doesn’t mean we refresh all the data, but data preview for all the queries.
Query Properties – very important section. This is one of the places where we can change the query Name, but more importantly, we can provide a Description for a better context. Enable load allows to decide if we want to load that query to the model. Useful for staging queries where we do only part of the transformations in one query and continue in other (or multiple ones). Include in refresh allows to say that this data is static and will not be updated, thus it doesn’t make sense to refresh it. Could be very useful for archived data, especially of a large volume.
Manage Columns – section you can remove/select columns. There is also a feature that allows you to search and go to specific column quickly, available also in View tab.
Reduce rows – features that allow use either to remove or keep rows specified by the given condition. For example, we can remove duplicates or rows that are empty or contain errors.
Sort – for currently selected column in Data Preview, it will sort the values in specified order.
Transform – some of the most useful features taken from Transform tab of the Ribbon.
Combine – unique section in Ribbon. Allow you either to combine tables, or multiple files when you connect to the data source that allows it (Folder/SharePoint/Blob, etc.). Append tables allows to add more rows to the table by combining two or more tables based on matching column names. Merge allows to add more columns to your table, by joining two tables together based on matching row values in specified columns. If you are coming from SQL world Append is similar to UNION while Merge is similar to JOIN.
AI Insights – allow us to transform data using Microsoft AI Features. You must be Power BI Premium user to work with Text Analytics and Vision, but you don’t require Azure Subscription, which is needed for Azure Machine Learning.
Text Analytics – here you can use one of 3 Cognitive Services:
Language Detection
Key Phrases Detection
Sentiment Analysis
Vision – there is only one function available, allowing to Tag Images based on the content.
Azure Machine Learning – the most complex feature, as it requires a Machine learning model built in Azure ML Studio. Data Scientists can build AI Models for various applications: fraud detection, spend classification, you name it. Later they must be enabled for you in Azure Portal.
Transform
It took a lot to go through a Home tab, even though it was on a very high level. It makes it almost impossible to do the same for Transform and Add Column tabs. Explaining these features require more time and they will be the topic for separate articles. For sure, what you must know about Transform tab is that all the functions available here, allow you to transform existing data structures, rather than create new ones. There are exceptions of course, like when you use Split Column function, which creates multiple columns out of single one. Let’s at least have a look at the available sections:
Table – features that allow you re-shape the table. Group by columns, transpose and more. The most drastic transformation can even get rid of entire table, and show a single scalar value, representing the number of rows of that table.
Any Column – transformations that will work on the columns regardless of their data type. Functions like Pivot or Unpivot columns I would rather see in Table section, but others make perfect sense. You can rename column, fill in blank values, detect data types or convert a column to a list.
Text Column – functions that will work only for the text values. You can split / merge columns, extract values based on range or delimiters, format text or parse it when you work with XML or JSON structures.
Number Column – functions allowing to perform basic and more complex mathematical operations on selected columns.
Date & Time Column – functions extracting only part of the Date/Time information like Year, Month, Week, Day, Hours, Minutes and so on. You can also transform entire tables using these functions by returning an Earliest or Latest dates available in the column.
Scripts – Power Query allows to run Python and R scripts directly in the tool. That comes with a lot of limitations, and with introduction of Microsoft Fabric we have a better way of working for guys that love to code.
Remember!Using Transform Functions like the ones available in Text Column, Number Column or Date & Time Column sections, will result in modifying the content of existing columns. If you wish to create new columns, go to Add Column tab in Ribbon.
Add Column
There is a lot of the same functions here as you saw in Transform tab, but as you probably guessed it, the end product is different. Here, instead of transforming existing column we create a new one. Either based on existing columns or completely from scratch. As always, let’s look at the available sections:
General – here you will find the most important functions to create new columns:
Column From Examples – heavily underestimated function. When you are not that familiar with Power Query to apply a proper function, you can manually provide expected result for a couple of rows. Power Query will take existing columns as input parameters and will try to figure out what is the transformation you want to apply and will suggest the solution for entire column.
Custom Column – semi-code approach. This function requires you to provide the code for the column logic. You might ask, why this is even here, since you can do the same in Formula Bar. Still, this function provides for you the remaining part of the code required by Power Query to create a new column and leaves only logic part for you:Figure 2. Example of Add Column code in Power Query.Here you can see entire code that is generated by Power Query to create a new column. Part that is required to be provided by Custom Column function is highlighted in red. If you can write code that’s great, still this function makes it slightly easier for you.
Invoke Custom Function – a bit more complex area. While invoking a function is relatively easy, it requires the function to invoke that must be provided with code. There is actually a way to convert an existing query into a function, thus allowing you to create function with no or little code. I am happy to present it someday.
Conditional Column – creates a column with if statement. Very simple one, nested conditions require to modify the generated code.
Index Column – Power BI creates an index for each row under the hood when data is loaded to the model. Still, we could an index column for the debugging process or to apply very creative transformations while working with unstructured data.
Duplicate Column – rather straight-forward function. Creates 1-2-1 copy of a selected column.
From Text – or rather should be “From Text Column”. Apart from Split Column we see the same options here as available in Transform tab.
From Number – almost the same set of columns available as in Transform tab. Statistics functions are greyed out, as they are “destroying” the table and return a scalar value only for selected column, therefore, there is no way of using them to create a new column.
From Date & Time – similar situation as with From Number section. Functions available in Transform tab are available here as well, except of those that were changing the structure of the table like Earliest/Latest.
AI Insights – the same set of three features as discussed on Home tab.
View
Very important tab as it contains a lot of useful features:
Layout – this section allows you to control additional elements visible in Query Editor like Querry Settings (with Query Properties and List of Applied Steps) and Formula Bar. I will be a like a broken record when it comes to Formula Bar and will always ask you to enable it. It is first step to start learning the code and you will quickly discover that it is very easy to modify generated code to achieve the expected result.
Data Preview – allows you to modify the content of Data Preview. Some of the features I discussed in previous article in Data Preview paragraph:
Monospaced – changes the way values are displayed in Data Preview, making sure that each character takes the same amount of horizontal space. Very useful for initial data discovery. Allows to spot patterns in values, leading spaces or other issues.
Show whitespace – allows to identify issues with text values, by showing invisible characters like linefeed/carriage return, or tab.
Column Quality, Column Distribution, Column Profile – features we know from previous article.
Columns – only one option available here: Go to column, the same as available in Home tab.
Parameters – enable/disable parameters for data sources and transformations. It is interesting that this setting simply resides here without much of an explanation. It is the same as available in Global settings of Power BI: File -> Options and settings -> Options -> Global -> Power Query Editor -> Always allow parametrization in data source and transformation dialogs.
Advanced Editor – for those of you who love working with code, here you can find entire code behind the query. Unlike Formula Bar where you see the code for a single step only.
Query Dependencies – very much outdated, but still very useful view of all your Queries and their dependencies. Extremely useful when looking at someone else’s solution, to make a better understanding of what happens under the hood. Power Query (Dataflows) online does better job presenting this information within Diagram View.
Tools
There is only one tools family here, responsible for diagnosing your query. Allows to better understand what happens in Power Query during data transformation. You can use this information to try optimizing your Query. As this is fairly advanced topic, I will cover it within separate article(s).
Help
Last tab available in Power Query Ribbon. You will find here links to official materials delivered by Microsoft, to help you learn this tool and Power BI in general. I was surprised that some of the links don’t work anymore, like Samples. Still, you will find a lot of useful content here, make sure to bookmark them all.
Guided Learning – part of the Microsoft Learning Portal where you can find Modules and Learning Paths related to Power BI. It gives you this nice interaction, where you can track progress of your learning, gain XP and promote your account to another level.
Documentation – it’s that raw version of Learning Portal, where there is simply a lot of articles describing available features.
Training Videos – a link to Microsoft Power BI YT channel. There is not much of training videos produced there nowadays, but all the monthly updates videos are posted there.
Community – very important link, as you can leverage Community’s knowledge to help you solve your own problems.
Community Galleries – you can find here report themes (report color schema), interesting solutions that people want to showcase, or Quick Measure Gallery where you can find many interesting DAX patterns.
Submit an idea – do you see a room for improvement? Start sending the feedback to Microsoft team. This portal allows you to do so.
To be continued
Covering Ribbon was a very big task. In next article I will talk about Queries, Querry Settings and Status Bar. Stay tuned. I hope to see you soon 🙂
As always, thank you for reading and see 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
