Need Solutions for Trailing Space Trimming Issue in Power Query

[Learner27]

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.

[mike_honey]

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]

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

[Learner27]

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

[Greg_Deckler]

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

[bsbs]

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.