Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I need to be able to extract the order reference number from a text column
The description contains the order reference number I need
I have had a look at similar threads and found this solution
= Text.Select([String],{"0".."9"})
Because there are more numbers in the description field this doesnt give me the result I need
Any help would be greatly appreciated - thank you in advance
Description | Result | Required Result |
Road Closure 1234569 Any Lane HF1 9JA - Date 06/01/21 | 123456919060121 | 1234569 |
Unplanned Road Closure 1234569 Nowhere Lane HF7 4JA | 123456974 | 1234569 |
Emergency Road Closure 1234569 Somewhere Lane HF16 5JA | 1234569165 | 1234569 |
Solved! Go to Solution.
Hi @KG1 ,
The whole M code is shown below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcw7C4MwGIXhv3LI3KJJNMEx9EKR0qGlkziE+nkBTYqtiP++Ii4Fx/fAc7KM3b0tcGj9Z+gJXMgoVgmMm3C1jnA5cySpwR5H+yWEKgh5IDjLdxl7undrnaMCmx83P9Y09/qjEaVmcaeO+orca9p2D9/Rn+QK8UpLglAx6qEhSL1MWqGq5VwRmqEZWZ7/AA==", 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}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Description", "Description - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Description - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Description - Copy.1", "Description - Copy.2", "Description - Copy.3", "Description - Copy.4", "Description - Copy.5", "Description - Copy.6", "Description - Copy.7"}),
#"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Description", "Description - Copy.1", "Description - Copy.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Removed Other Columns", "Description - Copy.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Description - Copy.2.1", "Description - Copy.2.2"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Split Column by Character Transition1",{"Description", "Description - Copy.2.1"})
in
#"Removed Other Columns1"
Actually, if the first string is always end with Closure, you could simply use this M syntax:
= Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Description], "Closure ", " "), type text)
Or the length of order reference number is fixed——always 7 ,then you could use LEFT() to extract the Result like this:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @KG1 ,
The whole M code is shown below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcw7C4MwGIXhv3LI3KJJNMEx9EKR0qGlkziE+nkBTYqtiP++Ii4Fx/fAc7KM3b0tcGj9Z+gJXMgoVgmMm3C1jnA5cySpwR5H+yWEKgh5IDjLdxl7undrnaMCmx83P9Y09/qjEaVmcaeO+orca9p2D9/Rn+QK8UpLglAx6qEhSL1MWqGq5VwRmqEZWZ7/AA==", 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}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Description", "Description - Copy"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Description - Copy", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Description - Copy.1", "Description - Copy.2", "Description - Copy.3", "Description - Copy.4", "Description - Copy.5", "Description - Copy.6", "Description - Copy.7"}),
#"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Description", "Description - Copy.1", "Description - Copy.2"}),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Removed Other Columns", "Description - Copy.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Description - Copy.2.1", "Description - Copy.2.2"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Split Column by Character Transition1",{"Description", "Description - Copy.2.1"})
in
#"Removed Other Columns1"
Actually, if the first string is always end with Closure, you could simply use this M syntax:
= Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Description], "Closure ", " "), type text)
Or the length of order reference number is fixed——always 7 ,then you could use LEFT() to extract the Result like this:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @KG1 ,
you can use this M snippet, this M snippet creates a new column called "Custom".
Paste this snippet in your query using the Advanced Editor (if you never used the Advanced Editor before, make a copy of your pbix).
Close the last line above the final in with a comma.
Paste the code snippet above the last in in your query.
As the step is called #"Added Custom", don't forget to use this name after the final in.
// the result column that contains the 1st number from the column description
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom",
// passes the current row to an inline function
(row) =>
let
// splits the column description into a list, the items are separated by a space
DescToList = Text.Split( row[Description] , " " ),
// counts the items o the list
NoOfItems = List.Count( DescToList ) - 1,
// extracts the first number of the list of numbers
TheFirstNumber =
// removes the nulls from the inner list
List.RemoveNulls(
// the inner, an interation across the items
List.Generate(
() => [x = 0 , y = try Number.From( DescToList{ 0 } ) otherwise null ]
, each [x] < NoOfItems
, each [x = [x] + 1 , y = try Number.From( DescToList{ [x] + 1 } ) otherwise null ]
, each [y]
)
){0}
in TheFirstNumber
)
in
#"Added Custom"
Here is a screenshot of the result based on the sample data you provided:
Hopefully, this is what you are looking for.
Regards,
Tom
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |