Forum Discussion
Data Cleansing Advice Needed
Anonymous,
You can solve it in below way:
1. As because there is no common logic between rows then you need to create a dictionary by yourself. So, create a table called 'Dictionary' which will store all correct machine names as below:
(example - link)
2. Create a new blank query and call it fnFuzzyLookup. Then paste below Power Query code there:
(Input as text) => let ListCount = List.Count(Dictionary[Brand]), Result = List.Generate( () => [ i = 0, t = Dictionary[Brand]{i}, k = if Text.Middle(Input,0,Text.Length(t)) = t then 1 else 0 ], each (if ([i] < ListCount and [k] < 2) then true else false), each [ i = [i] + 1, t = Dictionary[Brand]{i}, k = if [k] = 1 then 2 else (if (Text.Middle(Input,0,Text.Length(t)) = t) then 1 else 0) ], each (if [k] = 0 then "" else [t]) ) in List.Last(Result)3. Go to the main table (query) and then Add column -> Invoke custom function:
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Community ChampionAs MattAllington said.
These steps might help you preparing that list, as they remove all strings with numbers in them:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/BCoMwEER/ZfHclBi17bVFqRYFUSiCeAgSrBiiJF76941iT9XG6743s0xZWtdatiOrXxD2nEHaDwOTEPiIYIwTqzoYDGJWsv+K9/3jM6FPqWRKwU3SjkHAdQQVMbLPHnKOJ5PmEIzcWbpTMco3xFRpl3hxUmyco+gXOHgGa2SKZGvEnTLPhUSiYVL1nEPeim7emUBAGz1cr91WlkZbS2H+MHp7unZWXaaq6gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Machine Names:" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine Names:", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TextToList", each Text.Split([#"Machine Names:"], " ")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StringToListOfCharacters", each List.Transform([TextToList], (x) => Text.ToList(x))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "SelectStringsWithoutNumbers", each List.Select([StringToListOfCharacters], (x) => not List.ContainsAny(x, {"0".."9"}))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Reassemble", each Text.Combine(List.Transform([SelectStringsWithoutNumbers], (x) => Text.Combine(x, "")), " ")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Machine Names:", "Reassemble"})
in
#"Removed Other Columns"
and you'll get a table like this:
Community ChampionAnonymous
Seems we can also simply split by space/delimiter ...by splitting at the right most Space/Delimiter
- ImkeF
Indeed, that's much easier for the sample given.
... But will only work if there is just one string that contains numbers ;)