Hi @Anonymous,
That is due to delimiters. The code expects a period and/or a blank space. For TE74,12,EG re,102 the result is re,102 a space is found in between EG and re but did not consider the comma as a delimiter and so Text.Split was not able to produce the desired result.
Try the modified code below. If this still doesn't work, please provide a table that best represents how bad your data is. That way, the community can come up with a better solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZLLasNADEV/ZfBaDNHDSbuP61kECm5xFyGrEmggqzT/T2bkeRovfY50pRE+n7vZGbTmczRTBx12F1BEik4eUYumGnGu4oSOy/dxrMBUgHNmWCqGBhi3RpOZC0Lr54g1P27MWy77YIJUQdqCvAWlhdxmcgVpC3ILvz5O8Zj3W96zwMe1gVQqaQW1skAulcug7+EggARDKAXcUTmVVxzV/eYVNooABXqrI2CvfX3lwBSHtcPc50NXbldn9m2mdwz+ykmGRkkSSIBjpqhKr9u/h0yJrm0LLmWqpFq+hUaO80RdDj1ktxo46x9H9nH9/Xv+x4NdXg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Desired Result" = _t]),
#"Added Custom" = Table.AddColumn(Source, "Replace and Split",
each let
del = {" ".."/",":".."@","[".."'","{".."~", "#(cr)", "#(lf)"}, //list of possible delimiters based on ASCII table plus the new line separators
delreplacewith = List.Transform(del, each {_, " "}), //match each delimiter with blank space
delreplaced = List.ReplaceMatchingItems(Text.ToList([Test]), delreplacewith), //replace each delimiter with blank space
backtotext = Text.Combine(delreplaced) //convert the list back to text
in Text.Split(backtotext, " "), type list), //split text by blank space
//1 - check if each value in column "Replace and Split" can be succesfully converted to number otherwise null, 2 - remove the nulls, 3 -return index 0 if there is a value else null
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Number?", each try ( List.Select(List.Transform([Replace and Split], each try Number.FromText(_) otherwise null), each _ <> null){0} ) otherwise null, Int64.Type),
//if "Number?" is null get the second item from "Replace and Split" otherwise null
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Text?", each if [#"Number?"]= null then [Replace and Split]{1} else null, type text),
//and finally the floor nuber, if "Number?" is not null convert it to text and if null get the value from "Text?"
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Floor Number", each if [#"Number?"] = null then [#"Text?"] else Number.ToText([#"Number?"]), type text)
in
#"Added Custom3"
I have also converted the code above to a custom function. Place the code in a blank query and name it as desired. You can use the function by either using Invoke Custom Function feature or by creating a custom column. The function has only one parameter and that is the text you want to extract the floor number/letter from.
let
func = (text as text) as any=>
let
//list of possible delimiters based on ASCII table plus the new line separators
del = {" ".."/",":".."@","[".."'","{".."~", "#(cr)", "#(lf)"},
//match each delimiter with blank space
delreplacewith = List.Transform(del, each {_, " "}),
//replace each delimiter with blank space
delreplaced = List.ReplaceMatchingItems(Text.ToList(text), delreplacewith),
//convert the list back to text, all delimiters now replaced with blank space
backtotext = Text.Combine(delreplaced),
//split the text by blank space
tolist = Text.Split(backtotext, " "),
//check if a value in the list is a number and then select the first number
number = try List.Select(List.Transform(tolist, each try Number.FromText(_) otherwise null), each _ <> null){0} otherwise null,
//if does not contain a number, get the second text string - index position 1
notnumber = if number = null then tolist{1} else number
in notnumber
in func
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on
LinkedIn or hire me on
UpWork. Learn with me on
YouTube @DAXJutsu or follow my page on
Facebook @DAXJutsuPBI.
