CODEWITHSUNDEEP
powerbipowerquery
d82k
d82k

Reputation: 379

Power BI Query - Extract text between delimiters to a new column

I have a table that contains a text column in the following format:

"random text [ABC-######] other random text"

For all the rows containing "[ABC-" I would like to extract to a new column the "ABC-######" where ###### are random numbers at least 4 numbers but can be more.

this is what I got so far working:

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([TextColumn], "[ABC") then "FOUND" else "NotFound")

But I'm not able to extract the substring with numbers.

Can anyone help me please? Thank you dk

Upvotes: 2

Views: 9397

Answers (2)

Sergey Lossev
Sergey Lossev

Reputation: 1530


Less complicated version

let
    tbl = #table({"TextColumn"}, {
        {"qwer"},
        {"qwer [ABC-1234] asdf"},
        {"qwer [ABC-1234]"},
        {"[ABC-1234] asdf"}
    }),
    AddColumn = Table.AddColumn(tbl, "ExtractedNumber", each try Splitter.SplitTextByEachDelimiter({"[ABC-","]"})([TextColumn]){1} otherwise "None")
in
    AddColumn


Should look like this

enter image description here

Upvotes: 3

d82k
d82k

Reputation: 379

Solved using:

= Table.AddColumn(#"Changed Type", "Custom", each 
if Text.Contains([TextColumn], "[ABC-") then
(Text.Range((Text.Range([TextColumn], (Text.PositionOf([TextColumn], "[ABC-", Occurrence.First))+1, 14)), 0, (Text.PositionOf((Text.Range([TextColumn], (Text.PositionOf([TextColumn], "[ABC-", Occurrence.First))+1, 14)), "]", Occurrence.First))))
else "None")

As suggested in the comments

 Text.PositionOf

would result in being more fast.

Thanks

Upvotes: 1

Related Questions