Extracting specific pattern from text

[reinsken]

Hi,

I'm trying to extract from the column "Description" a specific string patter (in this example it's "BE12 3456 7890 1234" (19 characters) in Power Query. I've searche dozen of boards, but can't seem to find.

In Excel, i add 2 columns:

1) position_bank_account_number: i look for the position of the pattern by using the formula "SEARCH("BE?? ???? ???? ????",[@Description])"

2) bank_account_number: I extract the pattern by using the following formula: MID([@Description],[@[position_bank_account_number]],19).

Using my Excel formules in the example above, that would give me

However, in Power Query, I can't seem to find the proper way to obtain the same result. Any idea how?

[PhilipTreacy · Accepted solution]

Hi @reinsken 

This code extracts what you want.  Download PBIX file with code here.

let
    NumList = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DY07DoNQDASvsqKOEBIQkjYJV0iDKAyYyNL7IGNz/rxqm5nZaaq+clH0E6vrWSZQVmbD5eFwI+Man5x4RcyBTxPG4eGSRIojq1GN13h/oG+fDZqh7zA0bYfRhCJ29p+QIfBqJc2OUoAkY908otytcojVeLvSIuaKlBNYi7OzRk7m8YYlUNoKVs3zHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FirstNum", each List.PositionOfAny(Text.ToList([Description]), NumList)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Middle([Description], [FirstNum]-2, 19))
in
    #"Added Custom1"

Delete the FirstNum column, I just left it there so you can see how the code's working.

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Jimmy801 · Accepted solution]

Hello @reinsken 

you can search for "BE" in you text and then extract 19 characters if this is specific enough

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk48vC07JyUt6/C24kSFbDBDISexODsrJU3BydXQSMHYxNRMwdzC0kDB0MjYROHwNqAkRH1OIoihFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    Extract = Table.AddColumn
    (
        #"Changed Type",
        "Extract",
        each Text.Range(_[Description], Text.PositionOf(_[Description], "BE", Occurrence.First),19 )
    )
in
    Extract

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mahoneypat]

This can be done with RegEx using the R integration in Power BI.  This video explains how.

https://www.youtube.com/watch?v=9E3VsvFAge4

Easier, you can use your same approach with this column expression.

AccountNumber = var location = SEARCH("BE?? ???? ???? ????", TextTable[TextColumn],,0)

var acctnbr = MID(TextTable[TextColumn],location,19)

return acctnbr

Regards,

Pat

[reinsken]

Example data:

Description (where bank account number is located somewhere)

"Vivamus cursus laoreet vulputate. Donec molestie pulvinar porta. BE68 5390 0754 7034 Etiam feugiat lectus eu est interdum suscipit. Curabitur non erat fermentum, blandit."

position_bank_account_number = 66

extracted_bank_account_number = "BE68 5390 0754 7034"

[PhilipTreacy · Accepted solution]

Hi @reinsken 

This code extracts what you want.  Download PBIX file with code here.

let
    NumList = {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"},
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DY07DoNQDASvsqKOEBIQkjYJV0iDKAyYyNL7IGNz/rxqm5nZaaq+clH0E6vrWSZQVmbD5eFwI+Man5x4RcyBTxPG4eGSRIojq1GN13h/oG+fDZqh7zA0bYfRhCJ29p+QIfBqJc2OUoAkY908otytcojVeLvSIuaKlBNYi7OzRk7m8YYlUNoKVs3zHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FirstNum", each List.PositionOfAny(Text.ToList([Description]), NumList)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Text.Middle([Description], [FirstNum]-2, 19))
in
    #"Added Custom1"

Delete the FirstNum column, I just left it there so you can see how the code's working.

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.