132 Reshaping Your Data
Splitting columns
Often, different information is merged into one column and we need to define rules to split columns and separate the information. This recipe shows how you can split data by defining custom logic according to requirements.
Getting ready
For this recipe, you need to have Power BI Desktop running on your machine. You need to download the following file in a local folder:
• FactInternetSales CSV file
In this example, we will refer to the C:\Data folder.
How to do it
Once you open your Power BI Desktop application, you are ready to perform the following steps:
1.Click on Get Data and select the Text/CSV connector.
2.Browse to your local folder where you downloaded the FactInternetSales CSV file and open it. A window with a preview of the data will pop up; click on
Transform Data.
3.Browse to the OrderDate column and select it. Browse then to the Transform tab, click on Split Column, and then on By Delimiter as shown in the following screenshot:
Figure 4.40 – Split columns by delimiter
Splitting columns 133
4.The Split Column by Delimiter window will appear. Select Space as the delimiter from the drop-down list, flag Each occurrence of the delimiter, expand Advanced options, flag Columns, check that 2 is the number of columns, and then
click on OK:
Figure 4.41 – Split Column by Delimiter
134Reshaping Your Data
5.You will see that you end up with two columns, one with date values and the other with time values:
Figure 4.42 – Split columns output
There are other criteria to split columns. One of them is to split columns by the number of characters. Follow the next example to see how it works:
1.Go to the SalesOrderNumber column, click on Split Column, and then on By Number of Characters:
Splitting columns 135
Figure 4.43 – Split Column
2.The Split Column by Number of Characters window will pop up. Enter 2 as Number of characters, flag Once, as far left as possible, expand Advanced options, flag Columns, and click on OK:
Figure 4.44 – Split Column by Number of Characters