How to canonicalize strings in a field?

[Anonymous]

Hi All, I have a table of server responses I'm trying to report on, but while the same response comes back over and over again, it will have small variations: an IP address in one response that varies, a user ID in another that is referenced in the response, a hostname of the server giving the response that varies.

Is there any mechanism for canonicalizing these strings to make them consistent and reportable?

[v-lili6-msft]

Hi@ MHillyer

What is your data source and what form is the data, You could do these by built-in function in Transform of  Edit Queries. For example replace the value ,etc.

Please share some data sample and the expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Seward12533]

Can you provide some examples of the responses and the format you would like to see.  I have a report that I deconstruct URL into Site, BASE_URLs (Main Pages), Pages and then extracts parameters into columns. In M the Split command is very useful. 

[Anonymous]

A simpler example would be that if there's any IP address I'd like to replace it with a.b.c.d (55.55.232.33 becomes a.b.c.d and 76.233.23.44 becomes a.b.c.d).

Harder examples are when a message says "user not found: Bob" and "user not found: George" and getting those to collapse down, ideally without having to write a new rule for every new case.