136 Reshaping Your Data
The idea is to split the value SO from the numeric value 43697 (if we consider the values of the first row).
3. You will observe two new columns as the output of the splitting:
Figure 4.45 – Split Column output
Other methods to split columns include the following:
•By positions
•By lowercase to uppercase
•By uppercase to lowercase
•By digit to non-digit
•By non-digit to digit
You can define custom logic on how to split the data and leverage the main benefit on the Power Query side, which is to apply this step also to new appended data when refreshing the data source without the need to make this transformation at the data source level.
Extracting data
Similar to the previous recipe, you can extract subsets of data and information from columns in this recipe. In this recipe, we will see how we can easily extract information such as length, a selection of characters, or a range of data within the column. The idea is to show you how easy it is to perform these transformations quickly and intuitively.
Extracting data 137
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 Transform tab, click on Extract, and click on Last Characters:
Figure 4.46 – Extract
138Reshaping Your Data
4.The Extract Last Characters window will pop up, where you can enter how many characters to extract, starting from the last. In this case, enter 5 in order to extract the characters containing numbers and click on OK:
Figure 4.47 – Extract Last Characters
5. As an output, you will see that the same column was transformed:
Figure 4.48 – Extract output