DARTS : Transforming Data

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.

You might need to change data fields in order to perform data analysis. Here are some examples for transforming data.
Conditional Column
Custom Column
Extract
Replace Values
Remove Duplicates

Conditional Column

You can create a new column with your own rules.

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

Custom Column

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

Go to "Add Column" > Custom Column
Enter new column name and pick the column name from "Available columns"
When you want to concatenate, use '&'. Use double quote (") for adding text.
Make sure not to have any errors and click "OK"

Note: If any of combined column is null, the returned field will be null. You can use code below on 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 last two characters of STRM data.

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