Character.ToNumber is a Power Query M function that returns the number equivalent of a given character.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Character.ToNumber( character as nullable text ) as nullable numberDescription
The Character.ToNumber function turns characters into their equivalent unicode decimals. You supply a character or text (which can be null), and the function returns its numeric equivalent, according to the Unicode standard. A null value is returned if the text is null.
Examples
To understand how the Character.ToNumber function works, consider the letter ‘A’. When we use this as our input:
Character.FromNumber( "A" ) // Output: 65The function returns 65, which is the Unicode representation of the uppercase letter ‘A’.
A situation where you can use this function is when we dive into the world of lists and sequences. Consider the intriguing ‘a..b’ construct in list generation. This construct essentially maps onto the Unicode values, yielding a list of characters in the given range.
{ "a" .. "g" } returns { "a", "b", "c", "d", "e", "f", "g" }Under the hood, the list converts the ‘a’ and ‘g’ into their Unicode equivalents, before generating the sequence:
Character.ToNumber( "a" ) // Output: 97
Character.ToNumber( "g" ) // Output: 103.This correlation between characters and their Unicode representations is what makes the list generation possible.
This becomes even more apparent when we generate a sequence from ‘Z’ to ‘a’. In line with the Unicode values, Power Query provides the following sequence:
{ "Z" .. "a" } // Returns { "Z", "[", "\", "]", "^", "_", "`", "a" }
Character.ToNumber( "Z" ) // Output: 90
Character.ToNumber( "[" ) // Output: 91
Character.ToNumber( "`" ) // Output: 96
Character.ToNumber( "a" ) // Output: 97In essence, the Character.ToNumber function helps in transforming Unicode characters into their numeric equivalents. It’s helpful when working with list generation and other functions that rely on character manipulation.
Related articles
Learn more about Character.ToNumber in the following articles:
- Generating Random Numbers in Power Query
Generate random numbers, letters, dates, & symbols in Power Query. Find out how to prevent duplicates & generate lists of random values. » Read more - Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Related functions
Other functions related to Character.ToNumber are:
