Expression.Error: The 'count' argument is out of range
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: The 'count' argument is out of range.
Text.Range or Text.Middle asked for more characters than the text has from that offset. Fixed-position extraction breaks as soon as one value is shorter than expected.
Common causes
- Text.Range([Code], 2, 5) on a code shorter than 7 characters.
- An offset counted from 1 instead of 0.
- Trailing spaces trimmed after the positions were worked out.
How to fix it
- Use Text.Middle, which returns what's available when the count runs past the end.
- Prefer delimiter-based functions: Text.AfterDelimiter, Text.BetweenDelimiters.
- Check Text.Length first for a default value.
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
Text.Range("AB-12", 3, 5)Fixed:
Text.Middle("AB-12", 3, 5)Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- beginner~6 minPro
Split email addresses into user and domain (logistics)
Add the part before @ as User and the part after @ as Domain.
- beginner~6 minPro
Split customer names into first and last name (kite shop)
Split Customer at the space into First Name and Last Name columns.
- intermediate~6 minPro
Build fixed-width badge numbers (logistics)
Turn employee IDs like E104 into badge numbers like BADGE-000104.
- intermediate~7 minPro
Extract the reference code from email subjects (kitchens)
Each subject line contains a reference like (ref: KT-0042) somewhere in it. Pull out just the code.
Guides
- Power Query text functions cheat sheet (tested)
- Split column recipes: names, codes, addresses and lists