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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Learner27
Helper II
Helper II

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.

4 REPLIES 4
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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors