Using Unicode Characters in Power BI

Unicode Characters in Power BI

There are several scenarios to use Unicode characters in Power BI including but not limited to:

  • Creating simple KPI columns in Table or Matrix visuals
  • To show the status of a measure more visually like using starts
  • Using Unicode characters as icons in your reports representing the subject

Chris Webb explained some of the above scenarios here.

In this post I explain how you can use Power BI as a tool to generate almost all valid Unicode characters in Power BI. You can download the PBIT at the bottom of this post. Then you can copy the Unicode characters from Power BI and use them in all textual parts of your report like visual titles, text boxes and so on.

The Unicode planes start from 0 to 1,114,111 which is decimal equivalent of 0 to 10FFFF in hexadecimal numeral system. For more information on Unicode planes check this out.

So, a simple way to generate all possible Unicode characters is to generate a list of decimal numbers starting from 0 ending at 1,114,111. This way we generate a series of decimal numbers regardless of the gaps between starting and ending Unicode blocks. Then using UNICHAR() function in DAX to generate corresponding Unicode characters. With the following DAX expression you can easily generate a list and the corresponding Unicode characters:

Generate Unicode = 
SELECTCOLUMNS(
  ADDCOLUMNS(
      GENERATESERIES(0, 1114111, 1)
    , "Unicode Character"
    , IFERROR(UNICHAR([Value]), "Not Supported")
     )
, "Decimal Value", [Value]
, "Unicode Character", [Unicode Character]
)

Generate Unicode Characters in DAX

While generating Unicode characters with the above scenario is technically working, but, it is not good enough. With than 1 million rows, including all decimal numbers even those ones that are not valid, finding a Unicode character looks to be very hard.

So I thought of a better way of getting data from web that comes with Unicode Planes, Unicode Blocks and block range. One of the best online sources I found is Wikipedia.

Wikipedia, Unicode Blocks

So we just need to:

  • import data from the table in the above Wikipedia link
  • split the “Block Range” column to two columns containing Block Range Start and Block Range End
  • generate values between Block Range Start and Block Range End
Unicode Blocks Table from Wikipedia
Imported Unicode Blocks Data from Wikipedia

The only part which is not that straight forward is converting hexadecimal values to decimal values. Remember, UNICHAR() function only accepts decimal input values.

I started building a Power Query function to convert hexadecimal to decimal, but, it was buggy and not efficient at all. So I googled hex to decimal and found this article written by “Greg Deckler” that works very well, much better than what I was building.

Hexadecimal to decimal in Power Query
Converted Hex Values to Decimal Values

Now that I have the start and end Unicode Block Ranges in decimal, I can easily generate a list of values between the start and end ranges in Power Query using “List.Generate”.

List of Decimal Values
A New Column of Decimal Values Has Been Added

Expanding the “Unicode Decimal” list column gives us all decimal values in range that can be passed to UNICHAR() function in DAX.

Expanded List Column
Expanded List Column

After loading the data we just need to add a calculated column with the following expression:

Unicode Character = IFERROR(UNICHAR('Unicode'[Unicode Decimal]), "Not Supported")
Unicode Characters in DAX
Unicode Characters in DAX

Now you can easily find and copy a Unicode Character and use it in your report pages, visuals and so on without consuming a lot of storage. As you may already know, after September 2018 release of Power BI Desktop we can easily copy values from Table and Matrix visuals which makes it easy to copy Unicode Values.

The PBIT file is available to download, all you need to do is to open the file, right-click on any desired Unicode Character from the Unicodes Table then click “Copy value”.

Copy Value from Table in Power BI Desktop
Copying Unicode Character from Table

You can now paste the character in all textual parts of a report in Power BI including in the visual titles and Text boxes. You can even use the Unicode characters to rename a measure or column in the “Fields” tab from “Visualization” pain.

Unicode Characters in Measure Names
Unicode Characters in Renaming Measures in Visuals

Download the PBIT file from here.

More to read: Unicode Consortium official website.

5 thoughts on “Using Unicode Characters in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.