Forum Discussion
Extracting string Only if
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;[email protected] = 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.
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.
2 Replies
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.
