Create a Table with A to Z Character Values in Power BI Using DAX

I have explained previously that you can use GenerateSeries function in DAX to create a list of number, date or time, or even currency values. However, sometimes you might need to create a list of text values. Such as alphabet; from “a” to “z” lowercase, or uppercase. The good news is that you can do that with GenerateSeries also, but with a little trick. Let’s see how it works.

GenerateSeries for Numbers

I explained previously that you can create a calculated table in Power BI using DAX expression such as below, and get the result as a one-column table;

Sample Table = GENERATESERIES(1,10)

You can use GenerateSeries to create a list of dates, times, and currency values too, to learn more about it check out this article.

UNICODE: Returns the Code of the Character

You cannot use GenerateSeries to create a list of characters, unfortunately. so an expression like below would give you an error; “The arguments in GenerateSeries function must be of a numeric or date/time type”.

However, the trick is that every character has a numeric code assign to it in the Unicode world. the UNICODE function will give you the code of that character:

for example, an expression like below:

Code of the character = UNICODE("a")

as a measure, will return 97.

Which is the numeric code for character “a”.

Another good news is that these characters generate codes in the same order. so character “b” is code 98.

List of Codes

So, now you can simply create a table like this:

Codes = GENERATESERIES(
    UNICODE("a"),
    UNICODE("z")
    )

and you will have the list of all codes in one place:

UNICHAR: Returns the Character of the Code

The point, however, is not to have the list of codes, but to have the list of characters. You can use the UNICHAR function in DAX to return the character related to code.

For example, the expression below;

Character of the code = UNICHAR(97)

As a measure, returns the character “a”;

Convert the List of Codes to the List of Characters

Now, the last step is to convert the list of Codes to a list of characters, can be done using a SelectColumns function and UNICHAR function combined as below;

Alphabet = 
SELECTCOLUMNS(
GENERATESERIES(UNICODE("a"),UNICODE("z")),
"Character",
UNICHAR([Value])
)

and here is the result which is the list from “a” to “z”;

All Characters

You can modify the expression a bit and get a list of all basic Latin characters like this:

Alphabet = 
SELECTCOLUMNS(
GENERATESERIES(UNICODE("a"),UNICODE("z")),
"Character",
UNICHAR([Value])
)

Or even more characters. use the list of characters mentioned here as a reference.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.