Text.Split – Power Query 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-

• Global Super Store Dataset

Requirement-

Split the “Customer Name” column into first names and last names, see the sample of dataset for 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], " ")

Explanation-

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

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

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

Explanation-

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

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