Power Query Conditional columns

[vincentakatoh]

Hi, 

Trying to figure out the M code equivalent to add column,"final test result", in power query, 

- In column "s/n", look for equivalent s/n

- In column "station", look for equivalent station, 

- In column "test count", look for test count, 

output

- if "test count" is not highest, output =null

- if "test count" is higest, output= test result. 

Below is a sample data (actual data >1m rows)

[EvertonPessoa]

Hi @vincentakatoh,

I did not understand the comparison value, it follows Power M:

Table.AddColumn(last Steps , "Name Column", each if [Test Count] >  "?" then [test result] else "null")

[vincentakatoh]

Hi @EvertonPessoa, 

Thanks. Will try another example as below:

I can get the "First test results quickly, by using below

"First Test Result", each if [Attempts] = 1 then [test result] else null 

Nonetheless, struggling to add column "Final test result"

The intend is to get the final (last attempt) test result for each "student" and "subject". There is no limit on number of attempts for each subject. 

[MarcelBeug · Accepted solution]

You can use Group By:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"Srudent", "subject"}, {{"LastAttempt", each List.Max([Attempts]), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Attempts", "test result"}, {"Attempts", "test result"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllData", "Final Test Result", each if [Attempts] = [LastAttempt] then [test result] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LastAttempt"})
in
    #"Removed Columns"