Blog

Creating Columns in Power BI

You are currently viewing Creating Columns in Power BI
Data transformation is a process of extracting information from raw data. This is achieved by manipulating the data and creating new columns to bridge the information gap. In this blog post, a step by step guide to create new columns in Power BI has been discusses. These columns are created in Power BI’s query editor so first of all let’s discuss what a query editor is:

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. 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.

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.

A new window opens showing the interface for Microsoft Power BI Query Editor.

Creating Index Column

Index columns can be created inside Power BI’s Query Editor. Index columns serve as Row Counter for your data.

To create an index column:

1.Select the Sales Table from the Queries Pane.

2.Click on the Add Column in the Ribbon Menu.

3.Click on the arrow next to Index Column in the General Section and a drop-down menu appears.

4. Click on From 0.

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.

Creating Date Column

In Microsoft Power BI, 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.

 To create a date column:

1.Click on the Sales Table in the Queries Pane on the left.

2.Click on the Trans Date column.

3.Click on Add Column tab in the Ribbon Menu.

4.From the From Date and Time section, click on Date.

5.Now click on Year.

A selection list appears.

6.Click on Year.

A new column containing the year of the date vales in the Trans Date column appears at the end of canvas.

Creating Conditional Column

Power BI allows you to create Conditional Columns based on your input.

To create a conditional column:

1.Click on Sales Table in the Queries Pane.

2.Click on Add Column tab in the Ribbon Menu.

3.Click on Conditional Column in the General Section.

A pop-up menu appears.

1.In the section for New Column name, type “Priority”.

2.From the drop-down menu under the Column Name select “Urgent”.

3.In the section for Value, type “YES”

4.In the section for Output type “1”

5.In the section for Else type “2”.

6.Click OK.

A new column is created. This column assigns priority for each sales transaction based its urgency status.

Conclusion:

             Data Transformation is a process in which raw data is Transformed into a format that is suitable for fast and precise data processing and for efficient reporting.  Data Transformation involves a wide range of operations such as identifying data sources and data types, cleaning data by removing errors and duplicates along with enriching data and performing aggregations. Data transformation also involves creating columns to bridge the missing information gap. In this blog post, a step by step guide to create index, date and conditional columns has 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

 

Leave a Reply