By: Andy Kim

Business Intelligence Consultant @ Key2 Consulting

 

An important part in preparing data for business intelligence is manipulating the data into usable standard formats. In PowerBI, data can be manipulated both as it’s being brought into your report as well as after you’re bringing the data into Power BI. Let’s take a look at some of these common data manipulations that are built in PowerBI.

Preparation

Here is the sample data set we will use to explore some basic data manipulations.

sample data set, excel

From PowerBI Desktop, click on the “Get Data” button on the Home tab. Then select ‘Excel’ and select your file.

Microsoft PowerBI Desktop

Click on the checkbox next to the sheet containing the data. The “Edit” button gives you a chance to manipulate your data prior to importing.

Microsoft PowerBI Desktop

Sorting, Filtering, and Removing Fields With Empty Data Column Values

While we are here, notice that each column has a drop-down menu with some options. These options include removing rows with empty values and filtering for specific data values.

Microsoft PowerBI Desktop

Basic Transforms

Click on the “Transform” tab.

Microsoft PowerBI Desktop

This is where many of the basic data transforms are performed.

Microsoft PowerBI Desktop

 

Transpose

Click on the “Transpose” button. This will convert each row into a column.

*** Tip: Notice that the “Applied Steps” panel on the right keeps track of changes made to the data. In order to undo the Transpose operation, click on the ‘X’ to the left of the “Transposed Table” step. ***

Microsoft PowerBI Desktop

Detecting Data Types

Click on the “Name” column header to select that column. Notice that the Data Type has not been determined. Click on the “Detect Data Type” button.

Microsoft PowerBI Desktop

Notice that the Data Type has been detected as ‘Text’.

Microsoft PowerBI Desktop

Changing a Column’s Data Type

If you click on the “Data Type” button, you can change the data type of the column.

Microsoft PowerBI Desktop

 

Replacing Data Values

Highlight the “Gender” column and click on the “Replace Values” button.

Microsoft PowerBI Desktop

Click on the “Replace Values” button.

Microsoft PowerBI Desktop

Fill in the following and click “OK”.

Microsoft PowerBI Desktop

Notice the values have been changed.

Microsoft PowerBI Desktop

Extracting

Highlight the “Gender” column and click on the “Extract” button, then select “First Characters.” Notice that you can also extract the Length of the string, Last Characters, or Substrings in addition to the First Characters.

Microsoft PowerBI Desktop

Type in ‘1’ for the Count. This means we just want to keep the first character. Click “OK.”

Microsoft PowerBI Desktop

Notice that we now only have the first character in the “Gender” column.

Microsoft PowerBI Desktop

Splitting Columns

Highlight the “Hobbies” column then click on the “Split Column” button, then select “By Delimiter.

Microsoft PowerBI Desktop

Select “Semicolon” as the delimiter, then click “OK.” There is also an option to enter your own delimiter if it doesn’t exist in the drop-down.

Microsoft PowerBI Desktop

Notice that the column has been split into multiple columns with individual values.

Microsoft PowerBI Desktop

Merge Columns

Highlight the 3 “Hobbies” columns then click on the “Merge Columns” button. Change the separator to “Space” and click “OK.” This will remerge the columns we just split using a space instead of a semicolon.

Microsoft PowerBI Desktop

Notice that the columns have been merged back into a single column delimited by spaces.

Microsoft PowerBI Desktop

Formatting Columns

Highlight all 3 “Hobby” columns by holding down Ctrl or Shift. Then click on the “Format” button and select “UPPERCASE.”

Microsoft PowerBI Desktop

Notice the values have now been transformed into UPPERCASE. You can similarly apply the other functions such as lowercase and Trim.

Microsoft PowerBI Desktop

Extracting Date Parts

Highlight the “BirthDate” column. Click the “Date” button, then select “Year,” and “Year” again.

Microsoft PowerBI Desktop

Notice that the “BirthDate” column now only holds the birth year.

Microsoft PowerBI Desktop

In Summary

The examples above illustrate a few of the many built-in data manipulations that can be performed on your datasets. As you can see, the manipulations are very intuitive and easy to learn. I encourage you to download the free PowerBI software and explore these manipulations on your own!

Lastly, be sure to subscribe to our blog and stay tuned for more manipulation examples in PowerBI. We will be covering more advanced manipulations in the future!

Share
+1
Tweet
Share