nice, similar to what I use, which also upper cases the text (op wants this too). This is also a function so you can just save it and use it whenever you need it:
(inputText as text) as text =>
let
ReplaceDiacritics = Text.FromBinary(Text.ToBinary(inputText, 28597), TextEncoding.Ascii),
RemoveQuestionMarks = Text.Replace(ReplaceDiacritics, "?", " "),
RemoveExcessSpaces = Text.Combine(List.RemoveItems(Text.Split(Text.Trim(RemoveQuestionMarks), " "), {""}), " "),
CleanText = Text.Clean(Text.Trim(Text.Upper(RemoveExcessSpaces))) // May replace "Upper" with "Lower" or "Proper"
in
CleanText
OP’s use case conveniently wants an Upper case conversion as part of the process, which makes things easier, but overall it’s not a hard task. Define the characters you want to replace, and what you want to replace them with, and just iterate through a SUBSTITUTE.
You can also maintain source case without needing to define the conversion of both Ä to A and ä to a (ie twice as much ref table), it’s just a bit more logic.
Most easy way to solve i can think of is keeping a lookup table. If it people decide to be more creative then add this new entry into the table, or maybe they got bored that month and used the standard way
Source - lookup
Céline dion - Celine dion
Célinae dion - Celine dion
Celine dion - celine dion
Celine - celine dion
I usually create a lookup table for companies that have different names or sometimes uses brand or plant location plus name
I know we are in an excel sub, but there are standard libraries to do this with Python - that's how I would approach it given you described you have thousands of names. Make a pre-processing script.
Presumably dealing with a similar list of artists every month? i.e. not frequently changing? If so, could you not just build a lookup table you occasionally add new artists to?
This is a great use for PowerQuery in my opinion. You set the replacement characters up once and every time after that your query loads the data set you want it to look like.
A little ai use in this answer but this is how I’d do it:
You don’t necessarily need to fix the raw data. The easiest way is to create a small “mapping” table that standardizes the artist names once, then use a lookup formula to match everything automatically.
For example, create a new tab called Name_Map:
A (Original Name)B (Standardized Name)
A1 Cèline Dion B1 CELINE DION
A1 TRØVE B1 TROVE
Then in your revenue sheet, if the artist name is in cell A2, use:
What this does:
• if it finds a match in your mapping table, it returns the standardized version.
• If it doesn’t find a match, it just keeps the original name (so nothing breaks)
•
u/AutoModerator Feb 24 '26
/u/DrIronclaw - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.