Power Query M Functions: Extract Data Using Combination Of Text.Middle & Text.PositionOf

Welcome to Excel Jet Consult blog. In this short tutorial, we will learn how to extract values using Power Query M Functions.

Power Query is a data connection technology that enables users to discover, connect, combine and transform data sources to meet specific needs. We will use Power Query.

We want to extract the Regions such as WestEast from List values in column J

The first thing we want to do is convert the data from range to excel Table and name it Data

To do that, click inside the Data and press CTRL + T

Click on OK. Data converted to a Table and renamed Data. See below

The next thing is to load the Table into the Power Query Editor

To do that, in the Get & Transform Data group of the Data tab, click on From Table/Range. The Data immediately gets loaded to the Power Query Editor. See below

To extract the specific Regions from the List column:

In the General Group of the Add Column tab, click on Custom Column

The Custom column dialogue box below opens

In the New column name, provide Regions

In the Customer column formula, execute the formula:

=Text.Middle([List],3,Text.PositionOf([List],” “)-3)

Finally, click OK. We’ve been able to successfully extract the specific Regions from the List column using Power Query M Language

I hope you enjoy the tutorial, kindly comment below

Posted in Blog.

Leave a Reply

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