Split Feature | Power Query

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

Powered by BetterDocs

Leave a Reply

Your email address will not be published. Required fields are marked *