Forum Discussion
Clean column containing inconsistent data entered people
Hi,
I have a table of meetings and a column with topics. These topics are entered by the person conducting the meeting and aren't always standarized, but do contain a keyword. I read you can use Table.AddFuzzyClusterColumn to standardize any spelling or case issues and then look up values in a transform table. I tried this approach but I don't think I have enough data for the clustering algorithm to work. I was thinking if I can strip out the keyword in the Topics column and look up this value in the transformation table this would do the job.
Here is a simplified example. I want to standarized topics names in the "Topics" column by checking the "From" column in the TransformationTable and return the value in the "To" column. The final table will combine information from both tables.
How can this be done, or is there a better way to do this?
See PXI file here: https://drive.google.com/file/d/1YX8DmMVKRH4IYcKpus2Pyl9JsErvpeXa/view?usp=sharing
Hello Anonymous
you can use Table.FuzzyNestedJoin. Use the joinOptions to define treshold and other options to change the outcome. However you didn't specify that all content is always replaced with the value of the to-column or you need a replacement. If the second is the case you could use both of your column from and to to search for the "from" and replace it with the "to"
Here to code
let Topic = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKOUHBNSU9VitWJVjIC8uEcYyDHsag0KVHBOSc1sSggsbgYLG4C0oQQiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Meeting Id" = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meeting Id", Int64.Type}, {"Column1", type text}}) in #"Changed Type", Transformation = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck1JT1XSUXL0D9Z1jtB1dXF3VYrViVZyzklNLApILC4GyiHYsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}) in #"Changed Type", JoinFuzzy = Table.FuzzyNestedJoin ( Topic, "Column1", Transformation, "From", "New", JoinKind.LeftOuter, [Threshold=0.5, IgnoreCase=true] ), #"Expanded New" = Table.ExpandTableColumn(JoinFuzzy, "New", {"To"}, {"To"}) in #"Expanded New"THis is the output
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
9 Replies
Hello Anonymous
could you please make an example of your data and what's your expected output? Please post some usable data.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyJimmy801 I've updated my orginal post with a picture of a simplified example. Hopefully this can clarify the issue.
Hello Anonymous
you can use Table.FuzzyNestedJoin. Use the joinOptions to define treshold and other options to change the outcome. However you didn't specify that all content is always replaced with the value of the to-column or you need a replacement. If the second is the case you could use both of your column from and to to search for the "from" and replace it with the "to"
Here to code
let Topic = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKOUHBNSU9VitWJVjIC8uEcYyDHsag0KVHBOSc1sSggsbgYLG4C0oQQiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Meeting Id" = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Meeting Id", Int64.Type}, {"Column1", type text}}) in #"Changed Type", Transformation = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck1JT1XSUXL0D9Z1jtB1dXF3VYrViVZyzklNLApILC4GyiHYsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [From = _t, To = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", type text}, {"To", type text}}) in #"Changed Type", JoinFuzzy = Table.FuzzyNestedJoin ( Topic, "Column1", Transformation, "From", "New", JoinKind.LeftOuter, [Threshold=0.5, IgnoreCase=true] ), #"Expanded New" = Table.ExpandTableColumn(JoinFuzzy, "New", {"To"}, {"To"}) in #"Expanded New"THis is the output
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
I'm not sure the best mod to accomplish what you are looking for is the use of fuzzy functions. In the attached file you can find an attempt with the Table.GroupFyzzy function but if you don't make any preliminary transformations, in my opinion it doesn't work. The solution I propose makes use of the List.PosiztioOf function with the help of a comparison criterion that can be further refined. The simple criterion I applied is the one for which the key you indicate is contained in the string and in this case the string is "translated"
Hello Anonymous
did you check out the solutions proposed?
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy