Power BI training in Sydney, Melbourne, Perth, Brisbane and Adelaide

Power BI training across Australia

Creating Column by Example in Power BI

You are currently viewing Creating Column by Example in Power BI
Columns are created in Power BI to enrich the data. This is why creating columns is explicitly taught in our Power BI training in Sydney and Melbourne. These newly created columns fill the information gap in the data, making data analysis easier and more effective. These columns also increase the data load and must not be preferred over measures. However, these columns are suitable in some conditions. To learn more on this topic: https://powerbitraining.com.au/calculated-column-and-measures-in-power-bi-using-dax/ In this blog post, we will explore the feature of creating columns using the query editor instead of DAX. Here’s a step by step guide to learn creating some columns in Power BI: 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. In this blog post, a very powerful feature of Power BI query editor has been discussed. Using this feature, you can create columns but just providing a value that want in the column as an example. This works as a template for other values in the column. Before understanding how to create columns by example, you need to access the Power BI query editor by following the steps listed below.

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.
Power BI training in Sydney | Creating Column in Power BI

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

Creating Column by Example

Power BI allows you to create columns of your choice by providing examples of data manipulations needed on the data.  The process of creating columns by example in Power BI using the query editor can be functionally divided into two parts:

  • Setting the template to for creating similar column values.
  • Extending the template to all values in the column.

Creating template:

Let’s first create a template which will be used as an example to set values for other rows in the column.

To create a template for the column by example:

  1. Click on Country Table in the Queries Pane.
  2. Click on Add Column Tab in the Ribbon Menu.
  3. Click on Column by Example in the General Section.
Power BI training in Sydney | Creating Column in Power BI

A pop-up menu appears.

Power BI training in Sydney | Creating Column in Power BI

4. Type “United States, New York City” in the first row of Column1.

Extending the template:

Now that the reference value has been set, we have a template to create values for other rows in the column.

5.Click on any blank row of Column1.

Power BI training in Sydney | Creating Column in Power BI

Entire Column1 is filled with the values following the same pattern.

6. Click OK.

Power BI training in Sydney | Creating Column in Power BI

Conclusion:

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 using example in Power BI using the query editor 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