Power BI auto-trims my text. How can I prevent this?

[juan_pablo]

I have the following table with one column called "Original Text":

Original Text

OCN -TEST1

OCN -TEST2

OCN -TEST3

Note that the text before the "-" is composed of 4 characters one being a blank space that I must preserve. In power query I split the text by the delimiter "-" and then in a new custom column I measure the length of the column Original Text.1 to ensure the 4 characters are still there as follows:

Given that the result of the column "Length PoweQery" is 4, this means that the blank space is still there as expected. But when I clic "Close & Apply" and add a new custom column using DAX that measures the length of the column "Original Text.1", the result is 3 because the blank space has disappear:

Why after clicking "Close & Apply" Power BI trims column "Original Text.1" and removes the blank space that I need to preserve?

How can I prevent this auto-triming?

Thank you.

Here is the pbix file if you need it:

https://uniontecolog-my.sharepoint.com/:u:/g/personal/jreyesv_ecolog_com_co/EeqclBO-MwNLmK3cDeKJrakB... 

[njoppel]

Have you found a solution to this issue? We are having the same problem!

[Ahmedx]

pls see my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiF7THDkUSptdgmxD?e=dOrfi4

(x)=> Text.Replace(x,Character.FromNumber(32) ,Character.FromNumber(160))

[Ritaf1983]

Hi @juan_pablo 
You just need to add a space before the dash when you connect the string as part of a formula if I understand correctly

this is a formula for a custom column :
=[Original Text.1] & " "&"-"&[Original Text.2]
and here is a result :

Link to modified PBIX 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[juan_pablo]

Hi @Ritaf1983 thank you for your reply but I don't need to concatenate them again, I just need a column with the values including the blank space. The purpose of all this is to identify the entries in column "Original Text.1" that have length 4 even if the fourth character is a blank space. The real table has values without the blank space so I need to identify them using the LEN function in DAX.

I don't know why @v-yangliu-msft  accepted the solution. 

[Ritaf1983]

Hi @juan_pablo 
If the goal is to test the column why don't you test this issue from PQ?
Or just replace your space with an underscore for example:

[juan_pablo]

Hi, Thank you again but I don't need a workaround I just need the blank space in the column. Some DAX formulas rely on it.