Forum Discussion
Standardize column values that contain textual data
Hello,
I am trying to clean up a column that contains textual data. For instance, if my table "Materials" is structured like this:
I would like that table to look like this:
(Account # is not a unique identifier). Also, I considered creating a reference table with the standardized Description values that can then be merged with my "Materials" table using fuzzy matching - but that isn't the most efficient solution since there are hundreds of unique values in the Description column. I'm also not sure if there's any clustering options, but I'm open to the idea along with others like fuzzy matching if possible (other solutions are welcome as well!).
Thanks so much.
Hi fz1
If there is no logic that you can use as a condition or some kind of unique dictionary to merge maybe with some actions before, like splitting the column and merging by one of the strings like "generator" after you clean /trim/make it be with one type of chars because pq is case sensitive.
There are no other options.More information about fuzzy matching is here :
https://learn.microsoft.com/en-us/power-query/merge-queries-fuzzy-match
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
2 Replies
Hi,
You will have to create a 2 column table - Find what and Replace with. Even if you create this, there will be challenges because if we wish to replace "board" with "switch board" then a genuine Switch Board entry in your dataset will become Swith Swith Board. Nevertheless, create the 2 column table would definitely b required.
Hi fz1
If there is no logic that you can use as a condition or some kind of unique dictionary to merge maybe with some actions before, like splitting the column and merging by one of the strings like "generator" after you clean /trim/make it be with one type of chars because pq is case sensitive.
There are no other options.More information about fuzzy matching is here :
https://learn.microsoft.com/en-us/power-query/merge-queries-fuzzy-match
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
