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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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 trim, substitute, remove white space but they couldn't help. 

 

My wish is to extract only number from those strings.

 

 

I appreciate very much if you can solve this problem.

 

Thank you very much!

Regards,

Cindy

Sample

2 ACCEPTED SOLUTIONS
MarcelBeug
Community Champion
Community Champion

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)
Specializing in Power Query Formula Language (M)

View solution in original post

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.

Specializing in Power Query Formula Language (M)

View solution in original post

10 REPLIES 10
Aaron_Borns
Frequent Visitor

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?

 

 

It would be better to raise a new topic.

 

Anyhow: it looks to me the issue is with the addtional 0: 0x01F instead of 0x1F.

Specializing in Power Query Formula Language (M)
v-shex-msft
Community Support
Community Support

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 ))))

 

Capture.PNG

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
MarcelBeug
Community Champion
Community Champion

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)
Specializing in Power Query Formula Language (M)

Great thanks for your contribution. I'd like to ask is there any way to perform it using DAX expressions?

 

Merry Christmas and happy new year!

 

 

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.

Specializing in Power Query Formula Language (M)
blopez11
Resident Rockstar
Resident Rockstar

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

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi : Because there are hidden characters, I cannot transform them into either decimal or whole number. My sample is under my name in the first post

@BusinessAnalyst

 

Can you share the CSV.?




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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