Power Query: M 'if' Formula Help

[Tuta]

All,

= Table.AddColumn(Data0, "Custom", each if[Column1]="UPDATED" then "UPDATED" else "NEW")

Trying to add a custom column that drops in UPDATED if column 1 contains the word, if it doesn't then I want to drop in NEW. Not sure how to use wildcards in here either.





thanks!

 

[scottsen]

Your if statement looks ok to my untrained eye. Does that not work?

As far as wildcards... I don't think they are supported directly today.
https://cwebbbi.wordpress.com/2014/...-likewildcard-search-function-in-power-query/
https://social.technet.microsoft.co...r-expression-in-power-query-?forum=powerquery

 

[Tuta]

Currently - it drops in NEW in each row.

Column one actually has UPDATED as the first word in some rows, nothing in others.

So:
UPDATED Boring Boring Text Row1
Boring Boring Text Row2
Boring Boring Text Row3
UPDATED Boring Boring Text Row4


So in my super fancy new custom, I'm trying for:
UPDATED
NEW
NEW
UPDATED



Thanks!
john

 

[erinward11]

you're using = so it would have to match without any additional data in the field. If Updated is always the first word can you not search for the LEFT(Column, 7) = "Updated"

 

[miguel.escobar]

Text.PositionOf
and
Text.PositionOfAny

should help you accomplish that. You might want to read more about those M functions

 

[Tuta]

Miguel -- I most definitely need to read more.

= Table.AddColumn(#"Changed Type", "New_Updated", each if Text.Contains([Column1], "UPDATED") then "UPDATED" else "NEW")

The above is what I ended up with to get to where I needed (so far).




I'm going to leave this here for myself when I inevitably forget this again (and for others)
Learn About Power Query Formulas -- Click HERE