Forum Discussion
Conditional Trimming/replacing values
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
#"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]
2 Replies
Waldo35Helper I
#"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]
Thank you so much! Thats exactly what I was after.
