Add a "Right" formula to a new column

[gheyman]

Here is what I tried. New at this

if(right([@Commodity],1)<>"C" then RIGHT([@Commodity],4)else LEFT((RIGHT([@Commodity],5)),4))

Thanks for the help

 

[GlennUK]

What do you mean .... if you are trying to do a formula then you put comma instead of "then" and "else"?

 

[Norie]

Can you post some sample data and expected results?

P.S. You should probably take a look at the MID formula instead of trying to mix RIGHT/LEFT.

 

[gheyman]

I am new to adding formulas to Power Query. I am adding a new column that I want to have a formula in that uses the last 4 characters of whats in another column [Commodity]

unless the value in that column ends with the letter C. If it ends with the letter C then I was taking the last 5 characters (Left([Commodity],5) and then the first 4 of that formula

10010030 would be 0030 in the new column
101160C would be 1160 in the new column

In Excel this formula would have been:
=IF(RIGHT([@Commodity],1)<>"C",RIGHT([@Commodity],4),LEFT(RIGHT([@Commodity],5),4))

I am trying to figure out the equivalent to this formula to use in Power Query.

Hope that helps

 

[Norie]

Is it definitely Power Query you are using?

Power Query doesn't have LEFT/RIGHT formulas.

 

[RoryA]

You could use:

if Text.End([Commodity],1) <> "C" then Text.End([Commodity],4) else Text.Start(Text.End([Commodity],5),4)