Power Query M function IF AND transformation

[xonder]

I have the following data that needs some conditional formating with an M Function:

I would like to make the following transformation:

In [model], extract the text before the first "space". If the [brand] is "BMW" and the extracted text is 4 characters long, then retunr just the first of those characters.

model = if [brand] = "BMW" and Text.Length([model]) = 4 then each Text.Start( Text.BeforeDelimiter([model], " "), 1) else each Text.BeforeDelimiter([model], " ")

For example from above, the first line of [model] would become "3", but the fourth would become "Santa"

Thanks for your help

[PhilipTreacy · Accepted solution]

Hi @xonder 

Download example PBIX file with the following data and code.

Starting with this sample data

add a Custom Column with this line of code

if [brand] = "BMW" and Text.Length(Text.Start([model], Text.PositionOf([model], " "))) = 4 

then Text.Start([model], 1) 

else Text.Start([model], Text.PositionOf([model], " "))

to give this result

Note that in row 7 the result is X5 - is that what you want?

Also, you gave the example that row 4 would yield the result Santa so fromthat I've inferred that you wanted the first word returned if it'snot a a BMW and the first word in the model isn't 4 characters long.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[HotChilli]

You can do it in steps.

Duplicate the model column (so that we keep the original model column for comparison).

Split the new column on the left most space.

Add a new column with code like this:

if [brand] = "BMW" and Text.Length([#"model - Copy.1"]) = 4 then    Text.Start([#"model - Copy.1"],1) else [#"model - Copy.1"]