Forum Discussion
Extract 5 Consecutive Numbers from Text String
Hi Anonymous ,
Paste this code over the default code in a new blank query to see the steps I took to complete this:
let removeChars = List.Transform({65..90, 97..122}, each Character.FromNumber(_)), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY1LCgIxEETv0uuhMP2JcZ2QqAnMAYYsBHHlzvuDmd71q1dFHwdlvTBev/fnCxVLDKaN/KK5HVSVpSAisoaE8dQUdRUcvTDKXjtWfBUYeqv7uT/RdVMTBA2RccMovT6WdnadTRjBxLTds792oDn/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting String" = _t, Extract = _t]), chgTypes = Table.TransformColumnTypes(Source,{{"Starting String", type text}, {"Extract", Int64.Type}}), addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([Starting String], removeChars)), splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"), filterLengthFive = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 5) in filterLengthFiveSummary:
1) Create list of all letters, lower and upper case.
2) Use list to remove all text characters from starting string.
3) Split resulting values by "." delimiter into new rows.
4) Filter column on value length = 5.
This gives me the following output ([calcOutput] column):
Pete
Super UserHi Anonymous ,
Paste this code over the default code in a new blank query to see the steps I took to complete this:
let
removeChars = List.Transform({65..90, 97..122}, each Character.FromNumber(_)),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY1LCgIxEETv0uuhMP2JcZ2QqAnMAYYsBHHlzvuDmd71q1dFHwdlvTBev/fnCxVLDKaN/KK5HVSVpSAisoaE8dQUdRUcvTDKXjtWfBUYeqv7uT/RdVMTBA2RccMovT6WdnadTRjBxLTds792oDn/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting String" = _t, Extract = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Starting String", type text}, {"Extract", Int64.Type}}),
addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([Starting String], removeChars)),
splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
filterLengthFive = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 5)
in
filterLengthFive
Summary:
1) Create list of all letters, lower and upper case.
2) Use list to remove all text characters from starting string.
3) Split resulting values by "." delimiter into new rows.
4) Filter column on value length = 5.
This gives me the following output ([calcOutput] column):
Pete
Thanks BA_Pete ! I used this to extract times written as a string of four consecutive digits from a column of long-form text strings.
The changes I had to make:
- changing source to an existing table from another query
- space as delimiter instead of "."
- need to exclude other non-numeric characters from the string
My adapted code below in case it's useful for future readers.
= let
removeChars = List.Transform({33..47, 58..90, 97..122}, each Character.FromNumber(_)),
Source = My_Source,
chgTypes = Table.TransformColumnTypes(Source,{{"My_Text_Field", type text}, {"My_Output_Column", Int64.Type}}),
addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([My_Text_Field], removeChars)),
splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
filterLengthFour = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 4)
in
filterLengthFour