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