Reply
juan_pablo
Advocate III
Advocate III
Partially syndicated - Outbound

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

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:

 

juan_pablo_0-1684445516962.png

 

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:

 

juan_pablo_1-1684446296861.png

 

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

 

6 REPLIES 6
njoppel
New Member

Syndicated - Outbound

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

Ahmedx
Super User
Super User

Syndicated - Outbound

pls see my video

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

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

 

Ritaf1983
Super User
Super User

Syndicated - Outbound

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

Ritaf1983_0-1684559081489.png

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

Ritaf1983_1-1684559178536.png

 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Syndicated - Outbound

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. 

Syndicated - Outbound

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:

Ritaf1983_0-1686190534307.pngRitaf1983_1-1686190663767.png

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Syndicated - Outbound

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. 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)