Power BI Transforming Columns

Data imported into Power BI often require some work to be done to transform columns of your data. In this short blog post, we will look at some useful transformations that can be done to columns. If you haven’t followed our blog series on data preperation make sure to check them out under our blog post section on the Data Bear website.

Custom Columns

You can add a new custom column of data to your model from the Query Editor in Power BI Desktop. Adding a custom column is recorded as an Applied Step in the Query Editor. This is useful as it allows you to change the custom column or move it earlier or later in your Applied Steps. You can add a custom column by selecting the Add Column tab on the ribbon, and then select ‘Custom Column’.

Once selected the ‘Add Custom Column’ window will open. On the right-hand pane side, all the fields available in your data table will be displayed. This can easily be dragged into the ‘Custom column formula area’ for your calculations. Always remember to give your custom column a relevant name.

Create formulas for your custom column

Select a field from the ‘Available Columns’ list and select ‘<< Insert’ to add a field to the custom column formula. Another way of doing this is to double-click on a field in the list and it will be added to the custom column formula. Syntax errors are detected in real-time as you enter your formula to build your column. When there are no syntax errors a green checkmark will be displayed. If there is an error a yellow warning icon, along with the identified error is displayed. There will also be a link that you can select to push the cursor (in your formula) at the place where the error is detected.

Let’s add a very basic formula to show you how it works. In our S_Transactions table, we would like to see what is our performance against our ‘Line Total Target’. We will do this by dividing our  ‘Line Total EXCL’ field by our ‘Line Total Excl Target’ field to work out the percentage.

When you select OK, your custom column is added to the model, and the Added Custom step is added to your query’s Applied Steps.

Let’s rename our step to ‘Calculate Line Total %’.

If you want to modify your formula you simply have to double-click on the Added Custom step in the Applied Steps pane. This will open the Add Custom Column window again where your custom column formula is loaded ready to be modified.

Conditional Columns

Conditional columns allow you to create new columns based on values from a different column. A typical use case for this includes creating a set of “buckets” or “categories” based on ranges from a continuous value column. In our table, we would like to create categories for the QTYNET column, 0-10, 11-20 etc. To do this you select Conditional Column from the Add Column tab.

A new window will open called Add Conditional Column, here you can specify the following (we will include what we practically did for our example in brackets):

  • New Column Name (QTYNET Bracket)
  • The Column you are referencing (QTYNET)
  • The Operator (is less than or equal)
  • The value that your operator will reference to determine the output (our brackets)
  • Output, based on your operator and value (the value we would like to be returned)

When selecting OK a new column will be created called ‘QTYNET Bracket’. Under the applied steps window, an extra step called ‘Added Conditional Column’. Rename this to ‘Added QTYNET Bracket Column’. The result will be as shown in the below image.

Reordering Columns

You are able to reorder the order of your columns. Simply select a column and drag it to where you would like it to be or right click on the column where you are able to select where you would like to move the column to.

Duplicate Columns

Power BI allows you to duplicate columns, simply right-click a column and select duplicate column.

Index Columns

Power BI allows for an index column to be added. The index column will create a new column called ‘Index’. The default setting is that the column will start at 0 and increment each row by 1. You can customize your Index column by specifying the starting number and increments. We have selected our starting value as 1 and the increment value as 1 as well.

Split Columns

The ‘Split Column’ function is very similar to Excel’s ‘Text to Columns’ function. You are able to split a column by a range of different options as shown in the figure below.

After applying the ‘Split Column’ function it is important to follow the normal best practice of renaming the newly created columns and review the data type of the columns.

Did you know we offer Power BI training, view our options here.