Conditional Trimming/replacing values

[MitchTrott]

Hi,

This is a 2 part question. this is a snippet of what i have:

.

.

1.1.1.1

1.1.1.1

1.1.1.1.

1.1.1.1.

So my quesiton is this, How can I, firstly, remove the last "." without removing the ones between the 1s? Is there a function where i can remove the last character conditionally?

Secondly, how can i remove the lone "." without effecting the .'s between the 1's? Again, is there a condition where i can say "remove "." if its the only character in the cell?"

Thank you

[Waldo35 · Accepted solution]

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if 
Text.EndsWith([Column1], ".") then if Text.Length([Column1]) = 1 then null else Text.Start([Column1], Text.Length([Column1])-1) else [Column1])

 So this is the code i wrote in the advanced editor.

First it checks if it ends with a  .

if it does end with a dot it takes the entire string except the last character (unless the string is only 1 in length then just return null).  this is to filter out the loose . (record 1 and 2)

Result

Code in the custom column (not advanced editor)

if Text.EndsWith([Column1], ".") then if Text.Length([Column1]) = 1 then null else Text.Start([Column1], Text.Length([Column1])-1) else [Column1]

[MitchTrott]

Thank you so much! Thats exactly what I was after.