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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Learner27
Helper III
Helper III

Need Solutions for Trailing Space Trimming Issue in Power Query

Hello Everyone,

I'm having a scenario where I need to count records containing special characters. To achieve this, I've created a calculated column using the Search function. It works well, but there's an issue with trailing spaces being automatically trimmed from the column.

following is the calculated column that i have created for special characters.

StudentName CONTAINS SPECIAL CHARACTERS = 
SEARCH("%", Register[StudentName], 1,
SEARCH("~*", Register[StudentName], 1,
SEARCH("@", Register[StudentName], 1,
SEARCH("$", Register[StudentName], 1,
SEARCH("^", Register[StudentName], 1,
SEARCH("(", Register[StudentName], 1,
SEARCH(")", Register[StudentName], 1,
SEARCH("_", Register[StudentName], 1,
SEARCH("~#", Register[StudentName], 1,
SEARCH(" ", Register[StudentName], 1,
SEARCH("|", Register[StudentName], 1, BLANK())))))))))))))


Is there a way in Power Query to restrict or disable the automatic trimming of trailing spaces? I cannot create additional calculated columns in my dataset due to its large number of records.

I've explored various community posts on this matter. Some suggest editing the column using DAX to add spaces statically. However, my dataset consists of 2.5 million records, and it is dynamically sourced from SQL.

Any insights or suggestions would be greatly appreciated.

5 REPLIES 5
mike_honey
Memorable Member
Memorable Member

I just ran into the same issue. FWIW my data is geospatial shapes in WKT format. Due to their length, they have to be split into multiple rows (in PQ) and then reassembled by a Measure using CONCATENATEX. But trailing spaces in any cell get trimmed when the query is loaded, which can result in invalid WKT from that Measure.

My hackaround is to replace the spaces with a pipe "|" character in the query, then wrap my CONCATENATEX in a SUBST function to replace "|" with " " (space). This is viable in my case as "|" is never used in WKT syntax.  This preserves trailing spaces.

This is a bug IMO, Power BI should accurately store the data loaded. If we want to trim, we can do that in PQ. If they want to keep trimming as the default behaviour, they should add a switch to turn that off (keep trailing spaces).

I made a quick repro PBIX for this bug:
https://1drv.ms/u/s!AmLFDsG7h6JPiJFy4l080eHn3D5Zqg?e=YQ4e91



Greg_Deckler
Super User
Super User

@Learner27 So typically trailing spaces are not automatically done in Power Query, you have to actually perform a Trim operation. Text.Trim. Also, I'm not sure why trailing spaces is an issue, is it because of the search for " " and you want to include if the StudentName column contains trailing spaces?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Text.Trim is used to exclude the spaces. But in my case I need those trail ending spaces

@Learner27 Right, so don't do a Text.Trim operation then. To the best of my knowledge, Text.Trim does not happen automatically, you have to specifically perform a Text.Trim operation. That was the point I was making. Maybe if you post your Power Query code.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Text trim does happen automatically when the data is load from power query into the power bi data model. I have not been able to find a fix. You can have a value of "Word " in a column and once you load it into power bi, the text will be changed to "Word". It auto removes the trailing space. 

 

The user above isn't using a Text.Trim function, that is the whole concern they have raised. Power Bi auto trims it when it moves from power query and into the data model. 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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