Power Query M Functions: Extracting using Text.Start & Text.PositionOf

Welcome to Excel Jet Consult. In this tutorial, we will learn how to Text.Start and Text.PositionOf Power Query M Functions which are LEFT & FIND functions in Excel

Text.Start returns the first count characters of text as a text value

Text.Start Syntax                              =Text.Start(text as nullable text, count as number)

Text.PositionOf returns the position of the specified occurrence of the text value substring found in text

Text.PositionOf Syntax                  = Text.PositionOf(text as text, substring as text, optional occurrence as nullable number, optional comparer as nullable function)

 

Below is the caption of the data we want to extract the left-most names such as Larry, Geoffrey etc.

To extract the left-most, click on Add column and select Custom Column

In the Custom Column dialogue box, provide the new column name First Name

In the custom column formula, execute: Text.Start([Full Name],Text.PositionOf([Full Name],”,”))

Click OK

First Name successfully extracted from the Full Name.

 

Posted in Blog.

One Comment

  1. I’d like to extract the lines of a multi-line cell that do not begin with “-“. The lines that I wish to extract may have “-” in the name but will never begin with “-“.

    Can you assist?

Leave a Reply

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