Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Icon for Helper I rankHelper I
9 years ago
Solved

Can't remove invisible characters

Hi everyone,   I received those data from SQL server. They look normal in SQL but when importing to Power BI, some of them contain invisible characters that I can't remove them. I have tried to tri...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Based on an export from your report data and some investigation, I noticed that your text contains characters:

    8236 - large, ocean-going vessel

    8237 - left to right override

     

    When removing those, you're fine to proceed. Code from the formula bar, with data in Column1 resulting in an additional column with numeric data:

     

    = Table.AddColumn(#"Changed Type", "NumericData", each Number.From(Text.Trim([Column1],{Character.FromNumber(8236),Character.FromNumber(8237)})),Int64.Type)
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    To my limited DAX knowledge it is not possible in DAX.

    The SUBSTITUE function is close, but there is no function in DAX to convert unicodes to characters and I didn't succeed in copy/paste the special characters into the SUBSTITUTE function.

    Again, my DAX knowledge is limited.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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