Forum Discussion
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 excel text is as follows:
=IF([@[Customer Survey Status]] ="Completed", 1,(IF(Customer Survey Status="In Review",1,0))) & "," & IF([@[Partner Survey Status]]="Completed", 1,(IF([@[Partner Survey Status]]="In Review",1,0))) & "," & IF([@[Document Status]]="Completed",1,(IF([@[Document Status]]="In Review",1,0))) & "," & IF([@[Invoice Status]]="Completed",1,(IF([@[Invoice Status]]="In Review",1,0)))
I've tried the If statement and the text.contains methods but can't get either to work.
Result desired is something like 0,1, 0,1
I did post a 30 minutes ago but can't see it in the forums so reposting, sorry if this is a duplicate.
Thanks!
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") )
2 Replies
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") )