Print

Extract function in Power Query removes all the text? Here is how to fix it.

Sometimes when you extract text in Power Query, instead of removing the text you specified, it removes the entire text.
In this video I will show you when it happens and how to get around the problem.

Table of contents:

00:00 Intro
02:00 How to do it on rows
03:00 How to do it on column headers

Watch the tutorial:

Extract function in Power Query removes all the text? Here is how to fix it.

Scenario 1: Removing Prefixes on text on columns

If you want to remove the prefix on text in a specific column, for example remove A. and B.:

Remove prefix on columns Power Query

You can use the following M function on a new step (see video 02:00 for exact details):

=Table.TransformColumns(#"Transposed Table", {{"Columnl", each Text.AfterDelimiter(_,	type text}})

But that only works if you have prefixes on all rows. If you don’t, it will remove the text completely on the rows that do not have a prefix.

Scenario 1 Solution

So, how do we do it for our case? We will do it in two steps.

First, add an additional dot as a prefix:

This is code it will generate:

=Table.TransformColumns(#"Transposed Table", {{"Columnl", each "." & _ , type text}})

and once you have that, you can use the same transformation as before Extract after delimiter, but with a slight change, you will extract text starting from the end:

this is the code produced:

=Table.TransformColumns(#"Added Prefix", {{"Columnl", each Text.AfterDelimiter(_, ".",{0, RelativePosition.FromEnd}),type text}})

Removing prefixes on column headers

What happens if you want to do it on column headers instead?

In this example, our column headers have prefixes in some of the columns, not all.

Column headers with prefix

If you want to get rid of the Prefixes A. and B. you probably will use this M function:

=Table.TransformColumns(#"Transposed Table", {{"Columnl", each Text.AfterDelimiter(_,	type text}})

But you will get the same problem as before, so to solve it, we will use the same steps, but we will use different functions so they apply to column headers instead of columns.

So first we will add the prefix:

=Table.PrefixColumns(Source, ".")

and finally we will extract the text:

=Table.TransformColumnNames(Custom1, each Text.AfterDelimiter(_, ".",{0, RelativePosition.FromEnd}))

and that is all!!

Related content

Cant get enough of column transformations? Here you have a few more:

8 common column headers transformations you will need in Power Query
Was this helpful?

Reader Interactions

Leave a Reply

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

Table of Contents