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

Posted on by Reza Rad

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply