Parsing, Transforming, and Reshaping Data
Parsing Text
Text to Columns splits one field into many on a delimiter. Regex Tool and RegEx Parse extract patterns; RegEx Match filters on a pattern. DateTimeParse converts text to a real date using a format string like "%m/%d/%Y". Traps: a wrong format string yields nulls silently, and delimiters inside quoted text can cause extra splits.
Reshaping: Wide and Tall
Cross Tab turns rows into columns (long to wide), aggregating by a chosen method. Transpose turns columns into rows (wide to long), producing Name/Value pairs. These two are frequently confused in questions; remember Cross Tab = more columns, Transpose = more rows.
Aggregation and Grouping
Summarize groups rows and computes Count, Sum, Min, Max, and more. It reduces row count to one per group (unless grouping is omitted, giving a single total row). Combine Summarize with Join to attach group totals back to detail rows.
JSON and XML
The JSON Parse tool flattens nested structures into fields; XML Parse does the same for XML. Nested arrays may require multiple parse steps.
Worked example: A field holding "Red;Blue;Green" becomes three rows: Text to Columns (split to rows) or Transpose after splitting to columns. Rule of thumb: rows→columns = Cross Tab; columns→rows = Transpose; many rows→fewer rows = Summarize.