Advanced Parsing and Transformation
Parsing questions reward knowing which tool fits the text shape. The Text to Columns tool splits on a delimiter (with options for ignore delimiters in quotes, skip empty fields, and number of columns). The Regex tool has three modes: Parse (extract named groups into fields), Replace (substitute matches), and Tokenize (one row per match). RegEx Match is a filter (boolean). The DateTime tools convert strings to DateTime and back; the Formula tool can use DateTimeParse/DateTimeFormat with specifiers like %Y, %m, %d. Transpose turns columns into rows (key/value), and Crosstab pivots rows back to columns using a group-by, header, and value field.
Traps abound: Regex Parse with an optional group can yield nulls; greedy vs. lazy quantifiers change matches; Text to Columns on a delimiter that also appears inside quotes splits incorrectly unless you enable quote handling. Crosstab requires a unique combination of group-by + header, or you get an error; Transpose output has Name and Value columns that must be renamed before Crosstab. DateTime strings without a format fail to parse; locale matters.
Worked example: a FullName field holds "Doe, Jane". Use Regex Parse with pattern (\w+),\s*(\w+) and name the groups Last and First. For a log line with repeated tags, Tokenize mode with \[(\w+)\] produces one row per tag - far easier than nested Text to Columns. Rule of thumb: Tokenize for one-to-many, Parse for one-to-one extraction.