Extracting string Only if

[Xandman]

Hi

I'm trying to extract numbers that begin with 03 and 3.  I could use conditional column but my string contains multiple series that need to be extracted.

Example:

03123453 ; AA23143 ; 312345;AA@AA.com = 03123453 and 312345

31245523-31 ; 0234124-21 ; AASF#FG = 3124552331

I tried manually doing this with condition by example an split all of them into columns with delimiter ";" Then created one other column beside each to determine if the column beside them starts with 03 / 3 and return TRUE. 

Now my table columns looks like:

STRING          EXTRACT 1       STATUS       EXTRACT 2         STATUS        EXTRACT 3         STATUS

I need to combine then so I get 1 column that returns the extracted columns if the status is TRUE but I can't figure out how to do it.

[v-jingzhang · Accepted solution]

Hi @Xandman 

Create a blank query, open its Advanced Editor and paste below codes into it to check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjA2NDI2MTVWsFZwdDQyNjQBsSBi1o6ODo6Oesn5uUqxOtFKQEETU1MjY11jQ6ASA6AKoICukSFYZ7Cbspu7UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "SplitValues", each Text.Split([Column1],";")),
    #"Expanded SplitValues" = Table.ExpandListColumn(#"Added Custom", "SplitValues"),
    #"Trimmed Text" = Table.TransformColumns(#"Expanded SplitValues",{{"SplitValues", Text.Trim, type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Trimmed Text", each Text.StartsWith([SplitValues], "3") or Text.StartsWith([SplitValues], "03")),
    #"Remove Delimiter in Text" = Table.TransformColumns(#"Filtered Rows1", {{"SplitValues", each Text.Remove(_, "-"), type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Remove Delimiter in Text",{"SplitValues"})
in
    #"Removed Other Columns"

The important step is to split the strings into a list rather than multiple columns. Then expand the list column to new rows so all the split values will be in the same column. Then you can transform this column further.

Result

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[MFelix]

Hi @Xandman ,

The information is a bit confusing can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.