Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.