Compatibility Issues with Special Symbols Between Power Query and Power BI​

[Gino]

I created a custom column in Power Query to assess the values of [Attribute A]: displaying "⭕" when the value is >= 4 and "❌" when it's others, both representing Facebook icons.

Power QueryPower BI

While these symbols appear correctly in Power Query, upon loading the data into Power BI, I noticed that all entries in this field were uniformly shown as "⭕". Through testing, I found that the "⭕" symbol might be problematic, as substituting it with "✔️" resolved the issue. Has anyone else experienced something similar?

I suspect there might be a compatibility issue between Power Query and Power BI concerning certain special symbols, leading to such errors.

by Gino Liu

[BA_Pete · Accepted solution]

No, these aren't all from the same family of characters. Unicode characters are allocated into 'Blocks' and 'Planes'. Both 10004 and 10060 are in the '0 BMP' Plane, and the 'Dingbats' block. 11093 is also in the '0 BMP' Plane, but in the 'Misc Symbols and Arrows' Block. More info on this can be found here:

https://en.wikipedia.org/wiki/Unicode_block 

Therefore, it seems that Power Query does not contain all unicode characters within its 'language' set. It's not a bug. If it were up to me, PQ wouldn't contain any Unicode characters as it's just not designed for graphical output like this.

If you're really into using unicode symbols for reporting, I've attached a PBIX I made that you might find interesting/helpful. It picks up the latest unicode list from the link above, converts the codes from Hex to Decimal, and attempts to display them all in PBI with search capabilities. Enjoy 🙂

Pete

[v-nmadadi-msft]

Hi @Gino ,

As the provided workaround is the ony possibe solution for now.
We are going ahead to close this thread, If you have any other issues requerding your query we request you to raise a new thread in the Forum 

we’ll be happy to assist.

Thank you for being part of the Microsoft Fabric Community.

[v-nmadadi-msft]

Hi @Gino,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[GinoLiu]

When a column contains both ⭕ and ❌ values, everything looks fine in Power Query. But once the data is loaded into Power BI, all values in that column are displayed as ⭕.
If I replace ⭕ with ✔️, both ✔️ and ❌ display correctly in both PQ and PB.
This issue still persists. While it's now possible to work around it, that doesn’t mean the problem has truly been fixed.

[v-nmadadi-msft · Accepted solution]

Hi @Gino ,
 

While Power Query correctly displays both ⭕ and ❌, Power BI’s data model appears to misinterpret or simplify certain Unicode characters (like ⭕) during the data load, leading to inconsistent rendering.

We agree that while the workaround is effective, it doesn’t fully resolve the underlying issue.
For now, we’ll proceed with closing this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread or reopen this thread. We’ll be happy to help.

Thank you for your understanding and participation.

[v-nmadadi-msft]

Hi @Gino,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[GinoLiu]

When a column contains both ⭕ and ❌ values, everything looks fine in Power Query. But once the data is loaded into Power BI, all values in that column are displayed as ⭕.
If I replace ⭕ with ✔️, both ✔️ and ❌ display correctly in both PQ and PB.
This issue still persists. While it's now possible to work around it, that doesn’t mean the problem has truly been fixed.

[ZhangKun]

由于你的数据会到DAX中处理和展示，最好的方式将是否满足设置为1/0或True/False，或者在DAX中判断处理也可以，因为这是一个很简单的计算。

√或×这种符号，在书写公式时很不方便，而且在不同的客户端显示效果也可能不同。

通常，仅在最后展示的图表中才设置图标。

[GinoLiu]

我可以理解你的用意，在PQ進行值的判定，在PB進行值得顯示格式。我個人認為你的建議是好的。 但是對於我提出的錯誤現象比較無法解釋，我只是想要理解，一個符號字型，為何有的可以正常傳遞，另一些則不行。

感謝你的回覆。

[ZhangKun]

我进行了一些测试，或许是因为字体原因（只是简单的猜测）。DAX（或者说是Power BI）中的字体样式似乎和下面链接中的样式一致，比如这些符号✨⛳都会显示为彩色，这可能对你挑选符号有帮助。

https://www.compart.com/en/unicode/category/So

[Gino]

Hi, 張坤:
如同我回復他人的回答所述，

⭕ = Unicode 11093

❌ = 10060

✔️ = 10004

當PQ可以正常顯示⭕+❌，在PB一律顯示 ⭕+⭕。
如果我在PQ切換成✔️+❌，則PB也會顯示✔️+❌，你不顯得很神奇嗎?
我來徵詢大家看法，只是要確認我是否有遺漏什麼設定或是Power BI特殊的小秘密，看起來大家是沒有遇到過這類事情，我想PB Team大概也不會注意到這個小細節。PB是Unicode系統，因此不應該發生這種事情，出現一隻蟑螂的背後，可能是無限的蟑螂。

[BA_Pete]

Hi @Gino ,

Do not use Power Query to output symbols such as this. This isn't what PQ is designed for and you will always have nothing but trouble. Have your custom column output to the decimal representation of the unicode characters, then use a conversion in your data model something like the following to display your desired symbols in reporting:

// Calculated column
IFERROR(UNICHAR(YourTable[YourUniCharDecColumn]), "Not Supported")

Power BI should then be able to display these characters just fine:

Pete

[GinoLiu]

I totally get your suggestion—performing value checks in Power Query (PQ) and managing display formatting in Power BI (PB).
⭕ = Unicode 11093
❌ = 10060
✔️ = 10004

What puzzles me is why ❌ and ✔️ render without any issues, but ⭕ doesn’t. Aren’t these all from the same family of symbol characters?
Does Power Query have any documentation or rules explaining why "⭕" fails to display properly? Just genuinely curious.

Anyway, now that I’m aware of this bug, I’ll be sure to avoid using it going forward. I appreciate everyone’s input—thank you!

[BA_Pete · Accepted solution]

No, these aren't all from the same family of characters. Unicode characters are allocated into 'Blocks' and 'Planes'. Both 10004 and 10060 are in the '0 BMP' Plane, and the 'Dingbats' block. 11093 is also in the '0 BMP' Plane, but in the 'Misc Symbols and Arrows' Block. More info on this can be found here:

https://en.wikipedia.org/wiki/Unicode_block 

Therefore, it seems that Power Query does not contain all unicode characters within its 'language' set. It's not a bug. If it were up to me, PQ wouldn't contain any Unicode characters as it's just not designed for graphical output like this.

If you're really into using unicode symbols for reporting, I've attached a PBIX I made that you might find interesting/helpful. It picks up the latest unicode list from the link above, converts the codes from Hex to Decimal, and attempts to display them all in PBI with search capabilities. Enjoy 🙂

Pete