Help Using If Then

[NPandelos]

Good morning,

I'm new to using power query. I'm trying to create an if then statement in that will look at the first character in the column and if it matches a set of values it returns the cell value, if not it dropps off the last three digits of the cell. The code is giving me an error at the "then [Root.1]" I dont know why. Below is an example and the code.

      From             -->           To

3-000795-00       -->     3-000795

5-009870-01       -->     5-009870

AP-TESTPART      -->    AP-TESTPART

if (Text.StartsWith([Root.1],"A", "C", "F", "I", "L", "P", "R","S")
then [Root.1]
else Text.End([Root.1], Text.Length([Root.1])-3))

[NPandelos]

Awesome that worked. Thanks for your help.

[Vijay_A_Verma]

Use this

= if Text.Contains("ACFILPRS",Text.Start([Root.1],1)) then [Root.1]
else Text.RemoveRange([Root.1],Text.Length([Root.1])-3,3)

[NPandelos]

That worked good except that after I ran it i found another variable I need to get written in. I'm working with some rather large data sets. 90% of the remaining data that is a number value has a - 3 characters in from the end. I thought I could add a second if statement but it didnt work. Here is the code I thought would work.

if Text.Contains("ACFILPRS",Text.Start([Root.1],1)) then [Root.1] else
if Text.Contains("-",Text.End([Root.1]),3) then
Text.RemoveRange([Root.1],Text.Length([Root.1])-3,3) else
[Root.1] 

[Vijay_A_Verma · Accepted solution]

Use this

= if Text.Contains("ACFILPRS",Text.Start([Root.1],1)) then [Root.1] 
else if Text.Start(Text.End([Root.1],3),1) = "-" then Text.RemoveRange([Root.1],Text.Length([Root.1])-3,3) 
else [Root.1]

Actually, seeing your construction, first if can be totally removed. Do you have any data which will violate this?

= if Text.Start(Text.End([Root.1],3),1) = "-" then Text.RemoveRange([Root.1],Text.Length([Root.1])-3,3) 
else [Root.1]