Regex for extracting a particular string in the records of a Column

[Anne_Vyolett]

Need to extract a particular alphanumeric string from the records of a column.Add a new column in the report for the extracted values and add to the visual.

For Example:  Swift/Bic:STRULM23L is the string to be extract from every records of a column which has many alphanumeric values and add it to the another new column.This Swift/Bic:STRULM23L differs in every record as BIC/Swift:STRULM23 or Swift/Bic Code :VBTYSI890 ETC...

[Anonymous · Accepted solution]

Hi  @Anne_Vyolett ,

If you're getting consistent strings in your data, you can just consider using the calculated columns find() and mid() to get the strings

Create calculated column.

Column =
var _swift=
FIND(
    "Swift",'Table'[Column1],1,BLANK())
var _bic=
FIND(
    "Bic",'Table'[Column1],1,BLANK())
RETURN
IF(
    _swift<_bic,
    MID('Table'[Column1],_swift,19),
    MID('Table'[Column1],_bic,19
))

If the string to be fetched is not fixed, you may consider calling a Python script to process the data using regular expressions, check out the blog on steps:

Using regular expressions in power bi desktop - Microsoft Fabric Community

This is the related document, you can view this content:

Solved: Extraction of multiple 7 characters text substring... - Microsoft Fabric Community

Solved: Extract Values From a List of Records - Microsoft Fabric Community

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly