Forum Discussion
Logic problem?
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.
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.
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!
I will try that and thank you so much for the suggestion!