Extract first integer and thPowerQuery a way to remove the quantity number and specific word

[PaulTHR]

Hi - I need to create in PowerQuery a way to remove the quantity number and specific word "Piece" from a data set in Pbi PowerQuery.
I have managed to try a couple of methods including splitiing by delimiter but I lose the result completely when the word "Piece" is not in the record - looks like this:

Before	After	Unfortunately I am getting	But what I want is
2 Piece TypeA	TypeA	TypeA	TypeA
2 Piece TypeB	TypeB	TypeB	TypeB
2 Piece TypeC	TypeC	TypeC	TypeC
TypeD	TypeD		TypeD
2 Piece TypeA	TypeA	TypeA	TypeA
3 Piece TypeB	TypeB	TypeB	TypeB
3 Piece TypeC	TypeC	TypeC	TypeC
4 Piece TypeD	TypeD	TypeD	TypeD
TypeA	TypeA		TypeA
4 Piece TypeB	TypeB	TypeB	TypeB
4 Piece TypeC	TypeC	TypeC	TypeC
4 Piece TypeD	TypeD	TypeD	TypeD
TypeA	TypeA		TypeA
5 Piece TypeB	TypeB	TypeB	TypeB
5 Piece TypeC	TypeC	TypeC	TypeC
TypeD	TypeD		TypeD
5 Piece TypeA	TypeA	TypeA	TypeA
5 Piece TypeB	TypeB	TypeB	TypeB

I had thought about find/replace but the numbers can be anything so dont want a script for every number option.

[AlexisOlson]

I think you can also do this purely with the GUI by using Text After Delimiter using a space and scanning from the right rather than the left.

This produces code like

Table.AddColumn(
    #"Changed Type",
    "Text After Delimiter",
    each Text.AfterDelimiter([Before], " ", {0, RelativePosition.FromEnd}),
    type text
)

[Dinesh_Suranga · Accepted solution]

@PaulTHR 

First add a new column with following code.

Text.Remove( [ColumnName], {"0".."9"} )

Then replace " Piece ".

add space before and after Piece if there are space in existing column.

Thank you.

[PaulTHR]

Thanks very much that did the trick.