Forum Discussion
Anonymous
5 years agoNot applicable
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...
- 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") )
lbendlin
5 years agoSuper User
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")
)Anonymous
5 years agoNot applicable
Thanks so much, this really helped!