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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BassG
Frequent Visitor

Is it possible to Trim leading/trailing spaces using transform data?

Using an excel file as my source, there's a column called "Incident" which has the types of incident such as "Physical Assault", "Threatening behaviour", "Throwing an object" & so on.  There's 11 different incident types.
If there was more than one incident, then each incident is split up with a comma (e.g. "Physical Assault, Throwing an object").

 

So I went into Transform Data, used split column ("," as delimiter) & split it into rows so I could put a slicer on a graph, but it's got 2 instances of most incidents.
My thinking is that it's not removed the space from the start/end of any that were delimited.
Is there a way to resolve this without having to go into the excel file, recreating the split column & using TRIM?

Thanks in advance,

G

1 ACCEPTED SOLUTION
rsbin
Super User
Super User

@BassG ,

In Power Query there is a function called Text.Trim.
https://learn.microsoft.com/en-us/powerquery-m/text-trim

You can create a new Custom Column, apply this function to your original column then, if desired, delete the old column.

Hope this helps.

Regards,

View solution in original post

2 REPLIES 2
rsbin
Super User
Super User

@BassG ,

In Power Query there is a function called Text.Trim.
https://learn.microsoft.com/en-us/powerquery-m/text-trim

You can create a new Custom Column, apply this function to your original column then, if desired, delete the old column.

Hope this helps.

Regards,

BassG
Frequent Visitor

Thanks rsbin, that worked a treat.  😎

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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