Enter value in one column based on values of other columns

[Anonymous]

Hi, 

I have this table with the columns "project" and "number". I would need to add a column ("max") in which the row value is set to 1 if the value in column "number" is the max for the project, otherwise 0. In this example I've manually entered the 1's and 0's, but how can I achieve this with a formula?

[alena2k · Accepted solution]

hi @Anonymous you can try couple options below. There are probably better ones, but those will work.

Option 1 - without function: add custom column with maximum values for all project, filter by each projet and  compare numbers.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLGMwywnIMoWzTOAsiKwzXC9IzFwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "max", each Table.Group(#"Changed Type", {"project"}, {{"number", each List.Max([number]), type nullable number}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "max", {"project", "number"}, {"project.1", "number.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [project] = [project.1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"number.1", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "isMax?", each if [number] < [number.1] then 0 else 1)
in
    #"Added Conditional Column"

Option 2 - create function to getMax by parameter and invoke it for each row, compare results.
getMax

(project as any) => let
        Source = #"Table (2)",
        #"Filtered Rows" = Table.SelectRows(Source, each [project] = project),
        #"Grouped Rows" = Table.Group(#"Filtered Rows", {"project"}, {{"max", each List.Max([number]), type nullable number}}),
        #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"project"})
    in
        #"Removed Columns"

Main query

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzgLGMwywnIMoWzTOAsiKwzXC9IzFwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"project", type text}, {"number", Int64.Type}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "getMax", each getMax([project])),
    #"Expanded getMax" = Table.ExpandTableColumn(#"Invoked Custom Function", "getMax", {"max"}, {"max"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded getMax", "isMax?", each if [number] < [max] then 0 else 1)
in
    #"Added Conditional Column"

I personally prefer to use functions for tasks like this one.