Forum Discussion

PaulTHR's avatar
PaulTHR
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Dinesh_Suranga's avatar
    Dinesh_Suranga
    3 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

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
)