Problems with non printables characters

[fabiojoa]

I have in my database one column with Dates (Image 1). But, I don't convert this column, because it is Text and I need transform in Date Column. Counting your duration, I have lenghts with 10, 11, 12 and 13 characters. (Image 2). I split this column in 3 columns (Day, Month and Year) for in other step merge data. I have sucess in columns Day and Month, but my Year column have a non character value not removible. I replaced value in this column, spaces to nothing, continuous space to nothing and... nothing! (Image 3). Can someone help me? 

Image 1

Image 2

Image 3

[Vijay_A_Verma]

In a custom column, below should work for you (assuming your column is named Year). (Edit - You can also use PQ interface to extract first 4 characters from Year column)

Number.From(Text.Start([Year],4))

[fabiojoa]

It didn't work, unfortunately! I had the same problem.

[Vijay_A_Verma · Accepted solution]

I reproduced your problem from the link which you have mentioned. As you just need to extract digits and ignore any other characters (in this case non-printable ones), just use following formula on the problematic columns

=Text.Select([Column],{"0".."9"})

For example, I split one of your date columns and used following formula for the year

=Text.Select([Data da Divulgação.3],{"0".."9"})

Edit - After analyzing your data, I took one sample date - I found, it has following characters

U+200B

space i.e. character 160

then date

then once again U+200B

Now Clean command of PQ will not clean any of these as it cleans only Control characters and none of above are control characters.

Rather than cleaning i.e. removing, we will need to do reverse approach - Pick up only what is needed. You need only digits 0 to 9 and /.

Hence, for your all date columns, you simply use following to pick up only date part and ignore all non-printable hidden characters

=Text.Select([Column],{"0".."9", "/"})

Hence, you can convert your all date columns to true date format from following formula

=Date.From(Text.Select([Column],{"0".."9", "/"}))

Edit 2 - This single formula can be used to convert your date columns into right date columns

Date.From(Text.Trim(Text.Replace(Text.Clean([Column]),Character.FromNumber(8203),"")))

Here Text.Clean will remove non printable control characters.

8203 is Unicode for U+200B and leading/trailing blanks will be removed by Text.Trim

[fabiojoa]

Vijay! I Love You!!!! I'm very happy with your solution, works perfectly!

A big hug from Rio de Janeiro, Brazil!

Thanks very much!!!

[fabiojoa]

I treat these tables to remove the lines with subtotals, and I know how to perform operations to separate according to the operator, replace special characters, clean not printable text, etc... But not working!

[fabiojoa]

I'm fetching the data from two tables that are on the web page:

http://www.vale.com/brasil/PT/investors/equity-debt/dividends-ISE/Paginas/default.aspx

I import Tables 0 and 1 on this page, merge and then I need to transform the dates (which are in text format) into dates.

[BA_Pete]

Hi @fabiojoa ,

Select the column that you want to remove non-printable characters from.

Go to Transform tab > Format (dropdown) > Clean.

Pete

[fabiojoa]

Not working, Pete!

[Anonymous]

could you share some part of your table (non in a some image format)?