Trim Function

[Anonymous]

I wanted to delete specific strings from the data. But the data is not consistent.

sample data

123456_1

123456_10

1234568_01

1234567_1

I need to remove the last strings ( which are highlighted in BOLD) including "_" as well. how to do that?

[harshnathani]

Hi @Anonymous ,

It is always good to do this in Power Query.

But incase you want to do this as a Calculated Column

First Path =

var sub = SUBSTITUTE('Table'[ColumnValue],"_","|")

RETURN

PATHITEM(sub,1)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[rubinboer]

If you want to use a measure you can try

New String =

VAR EvalString = SELECTEDVALUE('Table'[Column1])

VAR LenTo = FIND("_" , EvalString, 1, LEN(EvalString)) - 1

RETURN MID(EvalString, 1, LenTo)

[Tahreem24]

Please try the below steps:

Step 1: Go to the power query editor

Step 2: Select you given column

Step 3:  then right-click on that column and choose the split column --> split delimeter --> then enter "_" in the delimiter.

 Step 4: Then you'll get two columns and so remove column which has values before "_".

[amitchandak]

@Anonymous , you can split column based on _

https://docs.microsoft.com/en-us/powerquery-m/text-split and use only first item on list

Try this

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[HotChilli]

In Power Query, when you don't know which function to use, you can use the following technique:
Select the Column,

Add a Column-> Column from Examples

Type in what each column value should look like i.e. each field without the text after the '_'

See what the Power Query engine suggests.

In this case it gets it bang on.