Transforming Data

This article provides information on how to transform your data in a Power BI report.

Detailed Information

Transforming Data using the Query Editor

• You might need to change data fields in order to perform data analysis. Here are some examples for transforming data.
• Go to the Query Editor by choosing Transform > Transform data. 
• Each action is automatically recorded under the "Applied Steps" box. When you need to modify the transformation you have done, go back to the step and change it from there. The "Applied Steps" box is very handy when you need to resolve errors.

Conditional Column

You can create a new column with your own rules.

1. Select "Add Column" > Conditional Column
   
    
2. Enter new column name, and enter rules. If more rules are needed, click "Add Clause".
   
3. Once all rules are entered, click "OK".

Custom Column

You can create your own column. In this example, combine more than one column.

1. Go to "Add Column" > Custom Column
   
2. Enter new column name and pick the column name from "Available columns"
    
3. When you want to concatenate, use '&'.  Use double quote (") for adding text.
   
    

4. Make sure not to have any errors and click "OK"
   
   Note: If any of the combined columns is null, the returned field will be null. You can use the code below in the formula box to fix the issue.

   = Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")

Extract

You can extract a part of your data. In this example, extracting the last two characters of the STRM data.

1. Click and highlight the column you want to extract. (In this example, "STRM" was chosen)
    
2. Go to "Add Column" > Extract > pick the function (In this example, "Last Characters" was chosen)
   
    
3. Enter the number of characters to keep
   
4. It will create a new column. You can change the name of the column.

Replace Values

1. Right click on the field column and choose "Replace Values"
   
    
2. Enter values
   

Remove Duplicates

1. Click a small table icon before the first column field starts and choose "Remove Duplicates."