How to process data clean in Power BI: A Brief of Transform Function

Visualization is a trend of data exploration. The key success indicator of visualization is cleaned data sets. To clean data, you can make it through scrips by Python, R, or you can use SQL at the query stage. However, if your role is a BI analyst or end-user, you may have no access right or hard code programming ability, if so, you need to learn how to clean or transform data in your BI tools. We are talking about clean data in Power BI in this article.

Transform function in Power BI desktop version

1. Duplicate function

You can use this function to duplicate your query. It is suitable for the purpose if you want to build a sandbox for any data clean or transformation. Click on the button manage and you can find duplicate function inside.

2. Split function

This function enables users to split the column into assigned columns. For example, if you want to split the director’s first name and last name, you can use this function. You can split the target column by space, denominator, # of characters, or digit/character.

3. Data type

If your raw data contains a column format that you want to leverage in the coming analysis and you like the column to be calculated or aggregate automatically by the BI build-in function, you need to transform the column into a proper format. For example, if you have date data in your query but it stored in a chart format but not date format, you had better modifying it. There are date, date/time, number, text, and binary format under this function.

4. Group by

We use this function to understand some ballpark numbers of categories. For example, in our Netflix data set, we want to understand the count for each product type, then we can apply this function.

For the group by category value, you can select Sum, Average, count, or other aggregate window functions you use in SQL

Review and modify your transformation

In the area of applied steps, you can see the manipulation we have done before. Here you can make changes or remove those changes you have done, as the double-check function provided here.

We introduce 4 high-frequent-use functions under the transform tab in Power BI. There are other functions that you can try to manipulate your data, for better use or more accurate purpose. Happy learning.