Creating Information, Custom and Standard Columns in Power BI
This blog is the second blog covering the topic of creating columns in Power BI desktop. Columns are created in Power BI desktop to enrich the data and extract information hidden in the raw data. This is one of the steps involved in data transformation.
Columns are created in the query editor. Let’s first understand what a query editor is:
Query Editor is an interface for Query transformation in Power BI. It allows you to seamlessly prepare data for BI tasks.
Microsoft Power BI is software as a service (Saas) platform which consists of:
Power Query
Deals with Cleaning of Data and uses “M language” to achieve its purpose.
Power Pivot
Deals with Calculations that are performed on the data and uses “DAX” to achieve its purpose.
Power View
This is the Data Visualization part of Power BI.
In this blog post, a step by step guide for creating information, custom and standard column has been presented.
To learn how to create index, date, and condition columns, refer to this step-by-step guide on creating columns in Power BI.
Getting Started
To get started:
1.Click on the Home tab in the ribbon.
2.From the External Data section, click on Edit Queries.
3.Select Edit Queries from the drop-down menu to enter the Query editor.
Creating Information Column Information Columns are used to extract some information from a given column. To create an information column: 1.Click on Sales Table in the Queries Pane. 2.Click on the Priority column. 3.Click on the Add Column in the Ribbon Menu. 4.Click on Information in the From Number section.
A drop-down menu appears.
5.Click on Is Even.
A new column is created that shows TRUE if the corresponding value in the priority column is even.
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.Creating 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. To create a custom column: 1. Select the Products table from the Queries Pane. 2.From the Ribbon, click on Add Column. 3.Click on Custom column in the General Section.
A pop-up menu appears
4.In the new column name section Type “Tax”
5.In the Custom Column formula section, type “0.095” and click on Product Cost in the available columns section on the right.
6.Click OK.
A new column Tax is created which calculates the tax on each value of the Product cost column.
Creating Standard Column Standard columns are created in Power BI to perform standard mathematical calculations at row level. To create a standard column: 1.Click on Sales Table in the Queries Pane. 2.Hold the control button on your keypad and click on the “Price” and “Quantity Sold” columns. 3.Now click on Add Column tab in the ribbon. 4.From the From Number Section, click on Standard. 5.Click on Multiply.
A new column is created which gives the total price for all the items sold.
Conclusion:
Calculated columns are created in Power BI to add the missing information. This is one of the steps in data transformation. In this blog, a step by step guide to create information, custom and standard columns is presented.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses: