Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Enter value in one column based on values of other columns

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?

 

celenius_0-1663931784677.png

 

1 ACCEPTED SOLUTION
alena2k
Resolver IV
Resolver IV

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.

 

View solution in original post

1 REPLY 1
alena2k
Resolver IV
Resolver IV

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors