Power BI

Extracting and Formatting a column in Power BI | Power BI training in Sydney

In our last Power BI training in Sydney, we spent a lot of time emphasizing on the importance of data transformation because data transformation lays the foundation for intelligent decision making. Data transformation refers to the steps applied on the raw data to extract useful information from it. Data transformation involves a wide range of operations such as extracting data from a column, formatting the column, creating new column, grouping the data using group by, removing blank rows, removing duplicates, changing data types and checking other repeatable work flows. The specific data transformation operation to be performed on the data depends on the data and the business problem at hand. We have developed an archive with different blogs covering different data transformation principles. Here’s the link to the archive: https://powerbitraining.com.au/category/data-transformation/

In this blog post, the first two principles of extracting and formatting column have been discussed. Extracting refers to the pulling of information from the data and formatting is the conversion of data into a format usable for data analysis. In the first half of the blog, the extracting of data has been discussed and the second half covers the formatting principles. Data transformation is performed using the Power BI’s query editor.

What is Query Editor?

Query Editor is an interface for Query transformation in Power BI. It allows you to seamlessly transform your data according to your requirements.

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.

Extracting Column

Values can be extracted from column using Extract Column option in the Power BI Query Editor.

To Extract a Column:

  1. Click on Product Table in the Queries Pane.
  2. Click on the Product Type/Product column.
  3. Click on Transform Tab in the Ribbon Menu.
  4. Click on Extract in the Text Column Section.
  5. Click on Text After Delimiter.
Power BI training in Sydney | Creating Column in Power BI

A pop-up menu appears.

6.Type “/” in the blank box. 

Power BI training in Sydney | Creating Column in Power BI

Please note that extract only works for columns whose data type has been declared as text.

Formatting Column

Microsoft Power BI allows you to format the column in the Query Editor.

To Format a column:

  1. Click on Product Table in the Queries Pane.
  2. Click on Product Line column.
  3. Click on Format in the Text Column Section.
  4. Click on UPPERCASE.
Power BI training in Sydney | Creating Column in Power BI

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.


Extracting and formatting are one of the two major steps of transforming the data into suitable format for data analysis. This is known as data transformation. Data Transformation is one of the core concepts of Data Analytics and data reporting. Data transformation refers to the series of steps performed on the raw data to make it suitable for analysis.

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

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *