In this article we will learn about split feature of power query. Split can be found on doing Right click on a column having data type as text.
The various options available under Split Column can be understood with the help example below.
In the given example we have name, table number, meal type, meal status, mobile number, customer location and pin code along with the expected date and day of visit.
As we can see, all the Names are mentioned in lower case, Table number is in Upper case, Meal Type is in upper case, Meal Status is in lower case and Customer Location is in proper case.
We need the desired output as per the image below:
To get the output as per the requirement, we have to perform following steps:
Step1: Import data into Power query
Step2: Right click on 2nd column and select Split column -> By Delimiter “-“.
The existing table will be updated with 2 columns containing Dates and Day
Step3: Right click on 1st column and select Split Column -> By Lowercase to Uppercase
The existing table will be updated with additional column containing name of customers.
Step4: Right click on 2nd column and select Split column -> By Number of characters -> ‘1’
The existing table will be updated with table number in the 2nd column.
Step5: Right click on 3rd column and select Split column -> By Positions -> ‘2’
Step6: Right click on 3rd column and select Split column -> By Digit to Non-Digit
The existing table will be updated with Pin code and location
Step7: Right click on 4th column and select Split column -> By Uppercase to Lowercase
The existing table will be updated with Meal type
Step8: Right click on 4th column and select Split column -> By Non-Digit to Digit
The existing table will be updated with Meal status and Mobile number
Step9: Rename all the columns as per the output table
