By: Andy Kim
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.
From PowerBI Desktop, click on the “Get Data” button on the Home tab. Then select ‘Excel’ and select your file.
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.
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.
Basic Transforms
Click on the “Transform” tab.
This is where many of the basic data transforms are performed.
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. ***
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.
Notice that the Data Type has been detected as ‘Text’.
Changing a Column’s Data Type
If you click on the “Data Type” button, you can change the data type of the column.
Replacing Data Values
Highlight the “Gender” column and click on the “Replace Values” button.
Click on the “Replace Values” button.
Fill in the following and click “OK”.
Notice the values have been changed.
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.
Type in ‘1’ for the Count. This means we just want to keep the first character. Click “OK.”
Notice that we now only have the first character in the “Gender” column.
Splitting Columns
Highlight the “Hobbies” column then click on the “Split Column” button, then select “By Delimiter.
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.
Notice that the column has been split into multiple columns with individual values.
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.
Notice that the columns have been merged back into a single column delimited by spaces.
Formatting Columns
Highlight all 3 “Hobby” columns by holding down Ctrl or Shift. Then click on the “Format” button and select “UPPERCASE.”
Notice the values have now been transformed into UPPERCASE. You can similarly apply the other functions such as lowercase and Trim.
Extracting Date Parts
Highlight the “BirthDate” column. Click the “Date” button, then select “Year,” and “Year” again.
Notice that the “BirthDate” column now only holds the birth year.
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!
Keep your data analytics sharp by subscribing to our mailing list!
Thanks for reading! Keep your knowledge sharp by subscribing to our mailing list to receive fresh Key2 content around data analytics, business intelligence, data warehousing, and more!
Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.