Conditional calculation of new column in Power Query

Hi sara11,

There are multiple ways to achieve this, here are two.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpPLEktUgAyzE0NgGRiolKsDkQmuDQ9ESxjoGNoBKRU4TLOGam5mcmJOQoRCmCdRuY6xkA6Nx1TRZQCxAQjkAkQeSeYrUDaRMcSSacTzFYgbapjZAG31AnFUrCsgSmqPoSNQI6xgQHEulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t, Parameter = _t, Result = _t, Unity = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Result", type number}}, "nl-NL"),
    AddedCustom = Table.AddColumn(ChType, "Custom", each [
        t = Table.FindText( ChType, [Sample] ),
        a = if [Parameter] = "Water " and [Unity] = "aa" 
            then List.Product( Table.SelectRows(t, each [Parameter] ="Water " or [Parameter]="Sugar ")[Result]) /10 
            else null 
        ][a]
    )
in
    AddedCustom

or a Group By

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpPLEktUgAyzE0NgGRiolKsDkQmuDQ9ESxjoGNoBKRU4TLOGam5mcmJOQoRCmCdRuY6xkA6Nx1TRZQCxAQjkAkQeSeYrUDaRMcSSacTzFYgbapjZAG31AnFUrCsgSmqPoSNQI6xgQHEulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t, Parameter = _t, Result = _t, Unity = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Result", type number}}, "nl-NL"),
    GroupedRows = Table.Group(ChType, {"Sample"}, 
        {
            {"t", each  Table.AddColumn(_, "Custom", (x)=> if x[Parameter] = "Water " and x[Unity] = "aa" 
                then List.Product( Table.SelectRows(_, (x)=> x[Parameter] ="Water " or x[Parameter]="Sugar ")[Result]) /10 
                else null )
            }
        }
    ),
    Combine = Table.Combine( GroupedRows[t] )
in
    Combine

with this result

I hope this is helpful