Home » Power Query M Functions » Text.Split – Power Query M Function

Text.Split – Power Query M Function

Text Split M Function

The Text.Split function in Power Query’s M language is used to split a text value into a list of text values based on a specified delimiter.

Let’s get stated-

Download the sample Dataset from below link-


Split the “Customer Name” column into first names and last names, see the sample of dataset for Customer Name.

Dataset Customer Name

Dataset Customer Name

Follow the steps below-

Step-1: Go to the Home Tab of Power BI Desktop and Open Power Query Editor  window by selecting “Transform data”.

Step-2: In the Queries pane,  select the “Orders” table.

Step-3: In the Home tab, click on “Add Column”  and choose “Custom Column”.

Step-4: In the “Custom Column” dialog box, provide a name for the new column, exp- “First Name”.

Step-5: In the “Custom column formula” box, enter the following expression, and click on “OK” to create the new column.

Text.Split([Customer Name], " ")


Text.Split function to split the values from the “Customer Name” column into a list based on the space delimiter (” “).

Text Split M Function

Text Split M Function

Now to get the first name from customer name column you can use the index with Text.Split function.

Text.Split([Customer Name], " "){0}


The {0} index is used to retrieve the first element (first name) from the resulting list.

Text Split with Index

Text Split with Index

Now repeat the above steps to create another column for the last name, and write below expression and change the index number from 0 to 1.

Because Text Split returns data as in list format and last name position index is 1.

Text.Split([Customer Name], " "){1}

Hope you enjoyed the post. Refer more M functions Tutorials

Leave a Reply