Forum Discussion

aso1's avatar
aso1
Helper II
6 years ago
Solved

if statement column

I have a column with different usernames. some contain some prefixes such as "DOMAIN\" and others dont have these prefixes. 

I want a custom column that attaches a text if X is something.  Exactly like this : 

 

Desired output :

if Name starts with 'NT SERVICE' or 'NT Authority' then type 'SQL System'

if Name starts with 'DOMAIN\' then type 'Windows Authentification'

if Name starts does NOT start with "DOMAIN" - then type SQL Authentification'

 

 

 

how can I achieve this? I would like to create a custom-column preferably. 

 

thanks!

 

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi aso1 ,

     

    Try this for a custom column on Power Query:

     

    if Text.StartsWith(Text.Upper([Column1]), "NT SERVICE")
    or Text.StartsWith(Text.Upper([Column1]), "NT AUTHORITY")
    then "SQL System"
    else
    if Text.StartsWith(Text.Upper([Column1]), "DOMAIN\") then
    "Windows Authentication"
    else "SQL Authentication"

2 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi aso1 ,

     

    Try this for a custom column on Power Query:

     

    if Text.StartsWith(Text.Upper([Column1]), "NT SERVICE")
    or Text.StartsWith(Text.Upper([Column1]), "NT AUTHORITY")
    then "SQL System"
    else
    if Text.StartsWith(Text.Upper([Column1]), "DOMAIN\") then
    "Windows Authentication"
    else "SQL Authentication"

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    HI aso1 ,

     

    You can create a calculated column using DAX as follows:

     

    ifContainsString = IF(CONTAINSSTRING(youTable[Name], "NT SERVICE") = TRUE() || CONTAINSSTRING(youTable[Name], "NT Authority") = TRUE(), "SQL System", 
    IF(CONTAINSSTRING(youTable[Name], "DOMAIN\") = TRUE() , "Windows Authentification", "SQL Authentification"))
     
    Thanks,
    Pragati