CODEWITHSUNDEEP
excelpowerquery
LOKE2707
LOKE2707

Reputation: 312

return conditional value in power query for a column, If value equal to maximum value of other column

I want to add an if else conditional column("E") in my power query table which will check if the value in my "C" column is equal to maximum value of that column or not. If true then the value of that field should be the value in column, else -1.

The excel equivalent formula for the column will be like

if(Max(C:C)=C2,C2,"-1")  #2 = row number
repeated in all the rows for E column

Macro equivalent code

   mx = Application.worksheetfunction.max(range("C:C"))
   for i = 2 to 20
   if  range("C"&i).value = mx then
       range("E"&i).value = mx
   else 
       range("E"&i).value = -1
   end if
   next i

On trying to use add conditional column, I am not getting the Statistic functions as options, in M-Language I am trying the with the below code 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type datetime}, {"C", Int64.Type}, {"D", Int64.Type}}),
    x = Table.Max(#"Changed Type","C")
in
    x

the x is giving a table instead of a value so I am not able to use it in if else statement. I am also trying to add custom column but not able to find any solution.

Below is the sample table for the reference.

enter image description here

Upvotes: 1

Views: 616

Answers (1)

horseyride
horseyride

Reputation: 21318

Close. Try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type datetime}, {"C", Int64.Type}, {"D", Int64.Type}}),
x = List.Max(#"Changed Type"[C]),
#"Added Custom" = Table.AddColumn(#"Changed Type", "E", each if [C] = x then [C] else -1)
in #"Added Custom"

or to save one step 

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type datetime}, {"C", Int64.Type}, {"D", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "E", each if [C] = List.Max(Source[C]) then [C] else -1)
in #"Added Custom"

Upvotes: 3

Related Questions