IF-THEN-ELSE in Power Query with substrings

[mtrevisiol]

Hi everyone, 

I am trying to convert a calculated column into a column realised via power query in M language.
The column in question extracts two letters from the 'Item' column, which contains a list of codes such as:
900KSUN4024EK
900RAY2224
800VSC002

The rule to follow is 'If the third letter is K, extract the fifth and sixth letter. Otherwise, extract the fourth and fifth letters).

In DAX I made the column with this formula:

Sigla codice = 
VAR ESTRAI = LEFT(
    KEY_MOHistory[Item], 6)
VAR SIGLA = RIGHT(ESTRAI, 3)
VAR INIZIALE = LEFT(SIGLA, 1)
VAR SIGLA_COD = IF(INIZIALE = "K", LEFT(RIGHT(ESTRAI, 2),2), LEFT(RIGHT(ESTRAI, 3), 2))
RETURN SIGLA_COD  

Can you help me realise the same column on power query? Thank you.

Here is the PBIX file: https://www.dropbox.com/s/y0ai83a9tp1ucr0/Extract%20Code.pbix?dl=0

[BA_Pete · Accepted solution]

Hi @mtrevisiol ,

Try this in a new custom column:

if Text.Range([Item], 3, 1) = "K"
then Text.Range([Item], 4, 2)
else Text.Range([Item], 3, 2)

It gives this output:

You can adjust the first numerical argument in Text.Range to change the offset (the start of when the two letters are taken from) if I've misunderstood exactly which characters you wanted.

Full example query for reference:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQw8A4O9TMxMDJx9VaK1QGLBDlGGhkZmYC5FgYGYcHOBgZGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Item", type text}}),
    addSiglaCod = Table.AddColumn(chgTypes, "siglaCod", each if Text.Range([Item], 3, 1) = "K"
then Text.Range([Item], 4, 2)
else Text.Range([Item], 3, 2))
in
    addSiglaCod

Pete