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] )
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.
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
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.
UPDATE: A big shout out to Rocco Lupoi who shared his Power Query code in the comments. His code is NOT recursive, so it performs better on larger amounts of data. Give Rocco’s code a go and see how it works in your scenario.
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”.
Expanding the “Unicode Decimal” list column gives us all decimal values in range that can be passed to UNICHAR() function in DAX.
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")
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”.
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.
More to read: Unicode Consortium official website.