Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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.
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
)
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.