IF formula based on format of value

[Caitlin_Knox]

I'm wondering if anyone has any suggestions for me. I have a column that contains multiple values. The format of these values determines the type (2 types). The determinig factor is an added '-' + digits at the end.

The type column is not existing in my data set, that's just to illustrate the difference. How can I include an IF Statement to return a column like 'Type'?  The rule would read 'Any value containing a dash is b and any value without a dash is a'

value	type
100	a
200	a
300	a
300-01	b
100-01	b
200-01	b
200-02	b

[Anonymous]

Hi @Caitlin_Knox

Assuming the first three values are numbers and not text then the code would be:

let
    source = #table( {"value"},
            {
            {100},{200},{300},{"300-01"},{"100-01"},{"200-01"},{"200-02"}
            }),
    #"added custom" = Table.AddColumn(source, "type", each try 
                       if Number.IsNaN([value]) then null else "a" otherwise "b")
in
    #"added custom"

[MarcelBeug · Accepted solution]

Or you can use the "Add conditional column" functionality.

Coincidentally, @Sean and I responded to a similar question today.

[Caitlin_Knox]

Thank you, this was just what I needed

[Anonymous]

this is weird!

there is no such option in the German Version!

Am I missing something?

@ImkeFhave you also observed this ??

[MarcelBeug]

The column should be formatted as text (or only contain text), then you will have the "Contains" option.

[Anonymous]

ok. Thanks @MarcelBeug