Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Daz474
New Member

Logic problem?

I wonder if anyone can come up with a simple solution to the following problem. I am sure its easy but it takes me forever. 

 

My data has only two fields - Account Code and Description I do not have any reference key or date field. There are millions of transactions and in only one of them there is a Smiley Face character in the text. This character breaks the load and I cannot get any data. I need to isolate which Account Code it belongs to.

 

I could try loading a range of account codes at a time and if it does not fail i know that I can exclude this range of Account Codes. but there are just too many for that. I cant do a between because it is alpha-numeric. Or is there a way to do that?

 

Additionaly, I want to make sure that I capture all of these characters in the case there are several at a time.     

1 ACCEPTED SOLUTION

Hi @Daz474,

Thank you for reaching out in Microsoft Community Forum.

Since the presence of a special character (like a smiley face) breaks the data load completely, the safest approach is to analyze the file in raw binary format before any parsing happens in Power BI.

Please follow these steps to safely identify the problematic row:

1.Use File.Contents to load the file as raw binary instead of as a table. This prevents Power BI from trying to parse corrupted characters.

2.Convert the binary into a list of lines using Lines.FromBinary.

3.Convert the list of lines into a table so you can inspect each one.

4.Add a column to flag lines that contain non-standard (non-ASCII) characters, such as emojis or symbols.

5.Optionally filter the table to only show the problematic rows, helping you isolate the account code.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

View solution in original post

11 REPLIES 11
v-pbandela-msft
Community Support
Community Support

Hi @Daz474,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

v-pbandela-msft
Community Support
Community Support

Hi @Daz474,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

Sorry for the delay! 

 

This doesn't work because if the character is in the load then it won't load anything. I need to find the characters without loading it.

Hi @Daz474,

Thank you for reaching out in Microsoft Community Forum.

Since the presence of a special character (like a smiley face) breaks the data load completely, the safest approach is to analyze the file in raw binary format before any parsing happens in Power BI.

Please follow these steps to safely identify the problematic row:

1.Use File.Contents to load the file as raw binary instead of as a table. This prevents Power BI from trying to parse corrupted characters.

2.Convert the binary into a list of lines using Lines.FromBinary.

3.Convert the list of lines into a table so you can inspect each one.

4.Add a column to flag lines that contain non-standard (non-ASCII) characters, such as emojis or symbols.

5.Optionally filter the table to only show the problematic rows, helping you isolate the account code.

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

v-pbandela-msft
Community Support
Community Support

Hi @Daz474,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

v-pbandela-msft
Community Support
Community Support

Hi @Daz474,

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 my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

v-pbandela-msft
Community Support
Community Support

Hi @Daz474,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps to resolve the issue;

1.In Power Query, you can filter out records containing specific characters. This will allow you to detect any rows with unusual or special characters, like the Smiley Face.

2.You could create a custom column that checks for non-standard characters in the Description field.

3.Add a custom column with a function to identify the presence of a Smiley Face (😊) or any other non-standard characters.

let
Source = YourSourceTable,
AddCustomColumn = Table.AddColumn(Source, "HasSmiley", each if Text.Contains([Description], "😊") then "Yes" else "No"),
FilteredRows = Table.SelectRows(AddCustomColumn, each [HasSmiley] = "Yes")
in
FilteredRows

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

BeaBF
Super User
Super User

@Daz474 Hi!

 

In Power Query you can add a custom column to select only safe characters:

= Table.AddColumn(#"Previous Step", "HasSpecialChar", each Text.Length(Text.Select([Description], {"A".."Z","a".."z","0".."9"," ",".","-",",","/"})) <> Text.Length([Description]))

Then remove the original column with unsafe characters.

 

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Community News image 1920X1080.png

Thanks for the suggestion. This won't work though because if there is a special character in the data load, it breaks it and nothing loads. I need to find the character without loading it.

@Daz474 

Ok, now it's clearer. So try to Load as Binary First, then Inspect Lines Before Parsing.

 

let
Source = File.Contents("C:\YourFile.csv"),
Lines = Lines.FromBinary(Source, null, null),
TableFromList = Table.FromList(Lines, Splitter.SplitByNothing(), {"Line"}),
AddCheck = Table.AddColumn(TableFromList, "HasNonAscii", each List.AnyTrue(List.Transform(Text.ToList([Line]), each Character.ToNumber(_) > 127)))
in
AddCheck

 

BBF


💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Community News image 1920X1080.png

I will try that and thank you so much for the suggestion!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.