Good Day,
how to do this in power query
if column Car1 , Car2 , and Car3 contain the word "Completed" then return "Completed" if one of the columns has different value or empty then return "not completed"
all of the 3 columns must be "completed" to return "completed" if any column contain any other word or empty return "Not Completed"
thanks in advance
Power Query add conditional column
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
-
- 2StarLounger
- Posts: 152
- Joined: 11 Jun 2012, 20:37
Re: Power Query add conditional column
Code: Select all
= Table.AddColumn(PreviousStep, "result", each (if [Car1]="Completed" and [Car2]="Completed" and [Car3]="Completed" then "" else "Not ") & "Completed")
Code: Select all
= Table.AddColumn(PreviousStep, "result", each (if List.IsEmpty(List.RemoveItems({[Car1],[Car2],[Car3]},{"Completed"})) then "" else "Not ") & "Completed")
Last edited by p45cal on 12 Jan 2023, 11:35, edited 1 time in total.
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Power Query add conditional column
my 3 cents
or IMHO "not completed" is not necessary, the result is obvious
Code: Select all
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Join = Table.AddColumn(Table.TransformColumns(Source,{{"car1", Text.Lower, type text}, {"car2", Text.Lower, type text}, {"car3", Text.Lower, type text}}), "Join", each if (List.Count(Text.Split(Text.Combine({[car1], [car2], [car3]}, ""),"completed"))-1) = 3 then "completed" else "not completed")
in
Join
Code: Select all
// Table1
llet
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
IS = Table.TransformColumns(Table.ExpandListColumn(Table.AddColumn(Source, "Status", each List.Intersect({{[car1]},{[car2]},{[car3]}}, Comparer.OrdinalIgnoreCase)), "Status"),{{"Status", Text.Lower, type text}})
in
IS
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
Re: Power Query add conditional column
thank u guys for your fast reply i tried many times to use your code i failed, it always give me error , i uploaded the excel file could u please show me how to do it and upload it back so i can study it and learn. thanks in advanced
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 152
- Joined: 11 Jun 2012, 20:37
Re: Power Query add conditional column
All four suggestions shown in attached.
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 456
- Joined: 05 Dec 2016, 13:48
Re: Power Query add conditional column
thank you so much , i understand what i did wrong now, thanks again
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Power Query add conditional column
just in case
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Power Query add conditional column
I'd use:
Code: Select all
=IF(LEFT([@Car1];1)&LEFT([@Car2];1)&LEFT([@Car3];1)="CCC";"";"not ")&"Completed"