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 ChampionIf there is no repeatable rule, then there is no getting around the fact that you will need to Interveine manually at some point. I suggest, create a list of all the unique entries in Excel. Add a column with the result you want. Load the table to power query and merge it on the description. Then extract the new column you need. You can also create an audit query that shows which items are missing values.
- ImkeF
As 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:
- Zubair_Muhammad
Anonymous
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 ;)
