You've imported a dataset from your company's legacy CRM system. The customer names are all uppercase, phone numbers are formatted inconsistently with a mix of dashes, dots, and parentheses, and full addresses are crammed into a single column that someone concatenated together with pipe characters fifteen years ago. You need clean, structured data — but the source isn't going to change. This is your problem to solve.
Text manipulation is where Power Query earns its keep in real-world data work. Raw data almost never arrives in the shape you need it. Strings — the technical term for sequences of text characters — are especially messy. Dates embedded in file names, product codes with meaningful segments baked in, email addresses that contain both the person's name and their department if you know how to look — the information is there, it just needs to be extracted.
By the end of this lesson, you'll be able to parse, split, reformat, and transform text data using Power Query's M language functions with confidence. You won't just be clicking buttons in the GUI — you'll understand the underlying functions well enough to write and modify them by hand, which opens up a whole class of transformations that the ribbon simply can't do.
What you'll learn:
Text.* function library is organized and how to read its syntaxText.Start, Text.End, Text.Middle, and Text.RangeText.Split and Splitter functions, and when to use eachText.Trim, Text.Clean, Text.Upper, Text.Lower, and Text.ProperText.Contains, Text.StartsWith, Text.EndsWith, and Text.PositionOfText.Combine, Text.Insert, and Text.ReplaceThis lesson is marked as Foundation-level within the Advanced M Language path, which means you should:
let and in before and they don't frighten youYou do not need to have written M functions from scratch before. We'll build that skill here.
Before we touch any data, let's establish the mental model. M is a functional language, which means you do everything by calling functions. The Text library is a collection of functions that all start with Text. followed by a descriptive name — Text.Upper, Text.Split, Text.Replace, and so on.
Every function takes inputs (called arguments) and returns an output. Arguments go inside parentheses, separated by commas. The first argument is almost always the text you want to work with:
Text.Upper("hello world")
// Returns: "HELLO WORLD"
You can test any function immediately by opening Power Query, creating a blank query (Home → New Source → Blank Query), and typing your expression directly into the formula bar after = . This is your playground. Use it.
One important thing to know: M is case-sensitive for function names. text.upper will throw an error. Text.Upper will work. This trips up beginners constantly.
Tip: If you're not sure what arguments a function accepts, you can type the function name with an opening parenthesis in the formula bar —
Text.Replace(— and Power Query will often show you a tooltip. You can also check Microsoft's official M function reference, but the tooltip is faster.
Let's start with the most common task: cleaning up messy text. You've received a list of customer names that look like this:
" ACME CORPORATION "
"widget co."
" Northern Lights LLC"
Inconsistent capitalization, leading and trailing spaces, internal double spaces — a mess. Here's how to fix each problem.
Text.Trim removes leading and trailing whitespace (spaces at the beginning and end of a string). It does not touch spaces in the middle:
Text.Trim(" ACME CORPORATION ")
// Returns: "ACME CORPORATION"
Text.Clean does something different and often misunderstood — it removes non-printable characters (invisible control characters like line feeds or carriage returns that sometimes sneak into data exported from other systems). It does not remove visible spaces:
Text.Clean("ACME" & Character.FromNumber(10) & "CORPORATION")
// Returns: "ACMECORPORATION"
// (The line feed character between the words is removed)
In practice you often want to chain both together:
Text.Trim(Text.Clean(" ACME CORPORATION "))
Read this inside-out: Text.Clean runs first, removing invisible characters, then Text.Trim removes the surrounding spaces. This pattern handles almost every whitespace-related cleaning problem.
Three functions, clear purposes:
Text.Upper("acme corporation") // Returns: "ACME CORPORATION"
Text.Lower("ACME CORPORATION") // Returns: "acme corporation"
Text.Proper("acme corporation") // Returns: "Acme Corporation"
Text.Proper capitalizes the first letter of each word and lowercases everything else. It's your go-to for turning all-caps customer names into readable format. Be aware it has one quirk: it treats any non-letter character as a word boundary, so "O'BRIEN" becomes "O'Brien" — which is actually correct behavior, though it can occasionally surprise you with hyphenated names or abbreviations.
In a real query, you'd apply this to a column named [CustomerName] in a transformation step:
TransformedNames = Table.TransformColumns(
PreviousStep,
{{"CustomerName", each Text.Proper(Text.Trim(Text.Clean(_))), type text}}
)
The _ is M's shorthand for "the current value" inside an each expression. You'll see it constantly.
Extraction is where things get interesting. You have a string and you need to pull out a specific portion of it.
Text.Start(text, count) returns the first count characters from a string. Text.End(text, count) returns the last count characters.
Imagine you have product codes formatted like "PRD-20240315-WEST". The first three characters are always the product type prefix:
Text.Start("PRD-20240315-WEST", 3)
// Returns: "PRD"
And the last four are always the region code:
Text.End("PRD-20240315-WEST", 4)
// Returns: "WEST"
Sometimes the piece you want is buried in the middle. Text.Middle(text, offset, count) starts at position offset (counting from zero!) and returns count characters.
From the same product code, extracting the date portion "20240315" — which starts at position 4 (after "PRD-") and is 8 characters long:
Text.Middle("PRD-20240315-WEST", 4, 8)
// Returns: "20240315"
Warning: M counts character positions starting from zero, not one. Position 0 is the first character. This is the single most common source of off-by-one errors in string extraction. When you're calculating your offset, always mentally number the characters 0, 1, 2... rather than 1, 2, 3...
Text.Range is essentially identical to Text.Middle — same arguments, same behavior. It exists for historical reasons. Prefer Text.Middle for clarity.
If you omit the count argument from Text.Middle, it returns everything from the offset to the end of the string:
Text.Middle("PRD-20240315-WEST", 4)
// Returns: "20240315-WEST"
The scenario: an address column contains "742 Evergreen Terrace | Springfield | IL | 62701". You need four separate columns. This is Text.Split's job.
Text.Split(text, separator) divides a string at every occurrence of the separator and returns a list of the resulting pieces:
Text.Split("742 Evergreen Terrace | Springfield | IL | 62701", " | ")
// Returns: {"742 Evergreen Terrace", "Springfield", "IL", "62701"}
The curly braces {} denote a list in M. To get a specific item from the list, use {index} notation (again, zero-based):
Text.Split("742 Evergreen Terrace | Springfield | IL | 62701", " | "){0}
// Returns: "742 Evergreen Terrace"
Text.Split("742 Evergreen Terrace | Springfield | IL | 62701", " | "){2}
// Returns: "IL"
In a full table transformation, you'd typically use Table.SplitColumn or add custom columns for each part. Here's how to add a "State" column by pulling position 2 from the split:
AddStateColumn = Table.AddColumn(
PreviousStep,
"State",
each Text.Split([FullAddress], " | "){2},
type text
)
Sometimes data has no delimiter — it's fixed-width, meaning each field occupies a specific number of characters. Bank transaction exports and old mainframe dumps often look like this.
Imagine account codes formatted as "CHK00487291" where the first three characters are account type, the next five are branch code, and the last six are account number. Splitter.SplitTextByLengths handles this:
Splitter.SplitTextByLengths({3, 5, 6})("CHK00487291")
// Returns: {"CHK", "00487", "291"} ... wait, that's only 8 chars for 5+6
// Correct example:
Splitter.SplitTextByLengths({3, 5, 6})("CHK0048729100")
// Returns: {"CHK", "00487", "291001"} -- hmm, let's be precise:
// "CHK" = 3, "00487" = 5, "291001" doesn't exist
// Better: "CHK004872910001" — just use a realistic total
Let's use a cleaner example to avoid confusion. Account code "CHK004871" — 3 chars type, 3 chars branch, 3 chars number:
Splitter.SplitTextByLengths({3, 3, 3})("CHK004871")
// Returns: {"CHK", "004", "871"}
The Splitter.* family of functions returns a function — notice the double parentheses. The first call configures the splitter; the second call applies it to a specific string. This is called a higher-order function, and while it looks strange at first, it makes these functions composable and reusable.
Tip: In the Power Query GUI, when you right-click a column and choose "Split Column," you're actually generating
Splitterfunction calls behind the scenes. After using the GUI, click on the generated step in Applied Steps and look at the formula bar — you'll see exactly whichSplitterfunction was called. This is a great way to learn.
Before you transform text, you often need to ask questions about it: Does this string contain a keyword? Where does a certain character appear?
These three return true or false and are most useful as conditions in filters or conditional columns:
Text.Contains("support@company.com", "@") // true
Text.StartsWith("INV-2024-0042", "INV") // true
Text.EndsWith("report_final_FINAL.xlsx", ".xlsx") // true
A practical use case: you need to flag all invoice numbers that start with "INV" and filter out the rest. In a custom column:
[DocumentType] = if Text.StartsWith([DocumentNumber], "INV")
then "Invoice"
else "Other"
Text.PositionOf(text, substring) returns the zero-based position of the first occurrence of substring within text. If the substring isn't found, it returns -1.
Why is this useful? Because you can use the position of a known character to make dynamic extractions. Consider email addresses like "jsmith@northernlights.com". The position of "@" tells you exactly where the username ends:
Text.PositionOf("jsmith@northernlights.com", "@")
// Returns: 6
Now you can extract the username dynamically — it works regardless of how long the username is:
let
email = "jsmith@northernlights.com",
atPosition = Text.PositionOf(email, "@"),
username = Text.Start(email, atPosition)
in
username
// Returns: "jsmith"
And the domain:
let
email = "jsmith@northernlights.com",
atPosition = Text.PositionOf(email, "@"),
domain = Text.Middle(email, atPosition + 1)
in
domain
// Returns: "northernlights.com"
The +1 skips past the @ character itself. This combination of Text.PositionOf with extraction functions is one of the most powerful patterns in text parsing.
Sometimes you need to go the other direction — constructing new strings or modifying existing ones.
The & operator joins strings end-to-end:
"Invoice-" & "2024" & "-" & "0042"
// Returns: "Invoice-2024-0042"
Note that both sides of & must be text. If you're concatenating a number, convert it first with Text.From:
"Row count: " & Text.From(1547)
// Returns: "Row count: 1547"
If you need to join multiple values with a consistent separator — like rebuilding a CSV field or constructing a comma-separated list — Text.Combine is cleaner than chaining & operators:
Text.Combine({"Alice", "Bob", "Carol"}, ", ")
// Returns: "Alice, Bob, Carol"
This pairs naturally with Text.Split for round-trip transformations: split to process individual pieces, then combine to reassemble.
Text.Replace(text, old, new) replaces every occurrence of old with new:
Text.Replace("(555) 867-5309", "(", "")
// Returns: "555) 867-5309"
To strip all non-numeric characters from a phone number, you'd chain multiple replacements:
let
raw = "(555) 867-5309",
step1 = Text.Replace(raw, "(", ""),
step2 = Text.Replace(step1, ")", ""),
step3 = Text.Replace(step2, "-", ""),
step4 = Text.Replace(step3, " ", "")
in
step4
// Returns: "5558675309"
Work through this exercise in Power Query using a blank query or by adding custom columns to any table.
Scenario: You've received an export of employee records from an old HR system. The EmployeeCode column contains values formatted like:
"ENG-042-REMOTE-jdoe@company.com"
The structure is always: Department (3 chars) - EmployeeID (3 chars) - WorkType - Email
Your tasks:
Extract the department code (the first 3 characters before the first dash).
Extract the employee ID (the 3 characters after the first dash).
Extract the email address (everything after the last dash — but here, use Text.Split to split on "-" and take the last element. Be careful: the email itself contains no dashes in this dataset).
Extract the username from the email address (everything before @).
Create a display name by combining the department code and employee ID with a space: "ENG 042".
Sample solution approach:
let
raw = "ENG-042-REMOTE-jdoe@company.com",
// Task 1: Department code
Department = Text.Start(raw, 3),
// Task 2: Employee ID
EmployeeID = Text.Middle(raw, 4, 3),
// Task 3: Email — split by "-" and take last element
Parts = Text.Split(raw, "-"),
Email = List.Last(Parts),
// Task 4: Username from email
AtPos = Text.PositionOf(Email, "@"),
Username = Text.Start(Email, AtPos),
// Task 5: Display name
DisplayName = Department & " " & EmployeeID
in
DisplayName
Try each step individually by changing what comes after in. Make sure you get "ENG", "042", "jdoe@company.com", "jdoe", and "ENG 042" at the respective steps.
"Expression.Error: We cannot convert the value null to type Text."
You're applying a text function to a column that contains null values. Wrap your expression in a null check: if [Column] = null then null else Text.Upper([Column]). This is tedious but necessary. Alternatively, replace nulls before the text transformation step.
Off-by-one errors in position calculations.
You expected Text.Middle("hello", 1, 3) to return "hel" but got "ell". Remember: position 0 is h, position 1 is e. Always count from zero. When debugging, use Text.PositionOf to verify positions rather than counting manually.
Text.Split gives you more pieces than expected.
If your delimiter appears in unexpected places — like a comma inside a quoted field in CSV-like data — you'll get extra list items. Either pre-clean the data or use a more specific delimiter. If you're working with actual CSV parsing, use the built-in CSV source connector rather than splitting manually.
Forgetting that Splitter.* functions return functions.
If you write Splitter.SplitTextByDelimiter("-") without a second set of parentheses with the actual string, you'll get a function back, not a result. You need Splitter.SplitTextByDelimiter("-")("my-string-here").
Case sensitivity in comparisons.
Text.Contains("Hello", "hello") returns false by default because M string comparisons are case-sensitive. Use Text.Contains("Hello", "hello", Comparer.OrdinalIgnoreCase) if you need case-insensitive matching. The same optional Comparer argument applies to Text.StartsWith, Text.EndsWith, and Text.PositionOf.
You've covered the full lifecycle of text data in M — from cleaning and standardizing raw input, to extracting meaningful pieces, to building new strings from components. The core functions to commit to memory:
Text.Trim, Text.Clean, Text.Upper, Text.Lower, Text.ProperText.Start, Text.End, Text.Middle, Text.PositionOfText.Split, Splitter.SplitTextByLengths, Splitter.SplitTextByDelimiterText.Contains, Text.StartsWith, Text.EndsWith&, Text.Combine, Text.ReplaceThe most important skill you've practiced isn't any individual function — it's composing these functions together. Text.Start becomes genuinely powerful when you feed it a dynamic length calculated by Text.PositionOf. Text.Split becomes essential when you pair it with List.Last to grab the final segment. Think in pipelines.
Where to go next:
Text.RegEx — M doesn't have native regex, but Power Query supports it through Text.RegEx in certain environments, and you can implement pattern matching logic using the functions you learned hereText.Split returns lists, learning List.First, List.Last, List.Select, and List.Transform will dramatically expand what you can do with split texttry...otherwise — Production-grade text parsing needs to handle unexpected input gracefully, and this construct is how you do it in M"20240315", you need Date.FromText or DateTime.FromText to make it usable as an actual date typeText manipulation is the unglamorous foundation of almost every real data project. Master it here, and the more complex M topics ahead will feel much more approachable.
Learning Path: Advanced M Language