Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help converting excel statement power query

Is it possible to create a custom column with a series of if statements that produce a concatenated string? I have done this in Excel but can't recreate it in power query for a custom column.  My exc...
  • lbendlin's avatar
    5 years ago

    You can pretty much directly translate this into Power Query code.  There are no brackets for if ... then ... else  but the rest is nearly the same.

     

    Something like 

     

     

    =Table.AddColumn(#"Previous Step", each 
    (if [Customer Survey Status] ="Completed" or [Customer Survey Status]="In Review" 
        then "1," else "0,") & 
    (if [Partner Survey Status]="Completed" or [Partner Survey Status]="In Review" 
        then "1," else "0,") & 
    (if [Document Status]="Completed" or [Document Status]="In Review" 
        then "1," else "0,") &
    (if [Invoice Status]="Completed" or [Invoice Status]="In Review" 
        then "1" else "0")
    )

     

    Or if you want to use List.Contains: 

     

     

    =Table.AddColumn(#"Previous Step", each
    let l = {"Completed","In Review"}
    in  
    (if List.Contains(l,[Customer Survey Status] then "1," else "0,") & 
    (if List.Contains(l,[Partner Survey Status] then "1," else "0,") & 
    (if List.Contains(l,[Document Status] then "1," else "0,") &
    (if List.Contains(l,[Invoice Status] then "1" else "0")
    )​