Logic problem?

[Daz474]

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.     

[v-pbandela-msft]

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]

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]

@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!