What is Query Editor?
Query Editor is an interface for Query transformation in Power BI. It allows you to seamlessly prepare data for BI tasks.
Getting Started
To get started:- Click on the Home tab in the ribbon.
- From the External Data section, click on Edit Queries.
- Select Edit Queries from the drop-down menu to enter the Query editor.
A new window opens showing the interface for Microsoft Power BI Query Editor.
Now that we have opened the query editor, we will discuss the different columns that can be created using the UI of query editor.
Index Column:
Index columns serve as Row Counter for your data. They provide incremental increase at each row as shown below.
You can create index column using three options:
From 0
If you select this option, an index column staring from 0 as the initial index is created.
From 1
If you select this option, an index column staring from 1 as the initial index is created.
Custom
If you select this option, a pop-up menu appears where you can select the value for your first index and the increment that you want on each step.
For a detailed guide: https://powerbitraining.com.au/creating-columns-in-power-bi/
Date Column
Date columns are created to assign date value to each row entry. Date calculations can be done on daily, weekly, monthly, quarter or yearly basis. As example of date column containing the year data is shown below.
For a detailed guide: https://powerbitraining.com.au/creating-columns-in-power-bi/
Conditional Column
Conditional columns evaluate the value of each row based on a condition. For example, the priority of a sales order can be set using the other fields in the data.
For a detailed step by step guide: https://powerbitraining.com.au/creating-columns-in-power-bi/
This column assigns priority for each sales transaction based its urgency status.
Information Column
Information Columns are used to extract some information from a given column. A new column is created that shows TRUE if the corresponding value in the priority column is even.
For a detailed guide: https://powerbitraining.com.au/creating-information-custom-and-standard-columns-in-power-bi-power-bi-training-across-australia/
Information columns can be created to show:
Even values
The result in the new information column will be TRUE if the corresponding value in the selected column is even.
Odd Values
The result in the new information column will be TRUE if the corresponding value in the selected column is odd.
Signs
The result in the new information column will be TRUE if the corresponding value in the selected column is positive.
Custom Column
Custom Columns are created in Microsoft Power BI to perform custom calculations. These are suitable for calculations which cannot be directly performed using Query Editor GUI. As an example, lets create a column to calculate the Tax amount using the Custom column feature.
A new column Tax is created which calculates the tax on each value of the Product cost column.
For a detailed guide: https://powerbitraining.com.au/creating-information-custom-and-standard-columns-in-power-bi-power-bi-training-across-australia/
Standard Column
Standard columns are created in Power BI to perform standard mathematical calculations such as multiply, divide, subtract, etc. at row level.
For a detailed guide: https://powerbitraining.com.au/creating-information-custom-and-standard-columns-in-power-bi-power-bi-training-across-australia/
As an example in the guide, a new column is created which gives the total price for all the items sold.
Statistical Column
Statistical calculations can be performed in Power BI using Statistical column feature. The image below counts the values of the product code in the Sales table.
In another example, we find the distinct product codes present in the sales table.
For a detailed guide: https://powerbitraining.com.au/creating-statistical-and-scientific-columns-in-power-bi-power-bi-training-in-melbourne/
Scientific Column
Scientific Columns can also be created in Microsoft Power BI. They are suitable for data that require scientific computations.
Using scientific column, you can perform the following computations:
- Absolute value
- Power
- Square Root
- Logarithm Factorial.
Statistics, Standard and Scientific column feature is available in both Transform and Add Column Tabs in the Ribbon Menu. It is important to choose the right tab to get the desired functionality. Transform tab transforms the very column on which the computation is performed. Whereas, Add New Column creates a new additional column where the computations are shown.
For a detailed guide: https://powerbitraining.com.au/creating-statistical-and-scientific-columns-in-power-bi-power-bi-training-in-melbourne/
Column by Example
Power BI allows you to create columns of your choice by providing examples of data manipulations needed on the data. As an example notice the pattern provided for the merge column.
The entire is filled with the values following the same pattern.
For a detailed guide: https://powerbitraining.com.au/creating-column-by-example-in-power-bi/
Extracting Column
Values can be extracted from column using Extract Column option in the Power BI Query Editor. There are different options to get extract the right data.
Please note that extract only works for columns whose data type has been declared as text.
For a detailed guide: https://powerbitraining.com.au/extracting-and-formatting-a-column-in-power-bi-power-bi-training-in-sydney/
Formatting Column
Microsoft Power BI allows you to format the column in the Query Editor.
Format column feature is available in both Transform and Add column tabs in the ribbon. It is important to choose the right tab to get the desired functionality. Transform tab transforms the very column on which format is performed. Whereas, Add new column creates a new additional column where the formatted values are shown.
For a detailed guide: https://powerbitraining.com.au/extracting-and-formatting-a-column-in-power-bi-power-bi-training-in-sydney/
Conclusion
Columns are created in Power BI using the Power BI’s query editor. The query editor comes with an easy to use, navigable user interface which provides the options to create different columns such as index column, date column, conditional column, standard column, custom column, statistical column, scientific column and column by example. The extracting and formatting operations can also be performed using the query editor. In this blog post, the different kinds of columns that can be created in Power BI using the query editor have been discussed.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
