Power Query add conditional column

[Mohammednt0]

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

[p45cal]

Code: Select all

= Table.AddColumn(PreviousStep, "result", each (if [Car1]="Completed" and  [Car2]="Completed" and  [Car3]="Completed" then "" else "Not ") & "Completed")

or

Code: Select all

= Table.AddColumn(PreviousStep, "result", each (if List.IsEmpty(List.RemoveItems({[Car1],[Car2],[Car3]},{"Completed"})) then "" else "Not ") & "Completed")

[hamster]

my 3 cents

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

or IMHO "not completed" is not necessary, the result is obvious

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

[Mohammednt0]

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

[p45cal]

All four suggestions shown in attached.

[Mohammednt0]

thank you so much , i understand what i did wrong now, thanks again

[hamster]

just in case

s666-PQ-compare three columns.xlsx

[snb]

I'd use:

Code: Select all

=IF(LEFT([@Car1];1)&LEFT([@Car2];1)&LEFT([@Car3];1)="CCC";"";"not ")&"Completed"