Forum Discussion
Extract first integer and thPowerQuery a way to remove the quantity number and specific word
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.
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.
3 Replies
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.
Thanks very much that did the trick.
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 )
