Can't remove invisible characters

BusinessAnalyst Do you have a sample? Have you tried to change the data type of the column to Whole Number, or decimal? This may remove any hidden character, or identify the problem values.

Did you try "Clean" as well?  Found in query editor, "Format" dropdown in the "Text Column" section

Just select the column, and go to the Transform tab

Hi BusinessAnalyst,

You can try to use below formula to remove the other characters:

Formula:

Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Text]), each if Value.Is(Value.FromText(_), type number) or _="." then _ else null ))))

Full query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\spilttext.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Text]), each if Value.Is(Value.FromText(_), type number) or _="." then _ else null ))))
in
    #"Added Custom"

Notice: Above formula only works on simple string, if your string is too complex, I'd like to suggest you use programming language and "Regular Expression" to replace the specific characters.

Regards,

Xiaoxin Sheng

When "Applying Query Changes" to a DirectQuery, I am experiencing something related to this: an error that says Failed to generate XMLA request. The changes cannot be submitted to the server. Error returned : '", hexadecimal value 0x1F, is an invalid character.'.

The 0x1F character is also known as US or Unit Separator. I could not find any US chars when pasting the M-code or datatable into Notepad++. What should I do?