Forum Discussion
IF-THEN-ELSE in Power Query with substrings
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
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 addSiglaCodPete
1 Reply
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 addSiglaCodPete