Forum Discussion

sara11's avatar
sara11
Helper I
2 years ago
Solved

Conditional calculation of new column in Power Query

I searched the forum and couldn't find a solution.   Well, here's what I want to do:   In the power query editor I want to add a new column that does the following for me:   Sample Paramete...
Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    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

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi sara11, different approach here. If you don't know how to use my query chceck link at the bottom of this post.

 

Result

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]),
    ChangedTypeAndTrim = Table.TransformColumns(Source,{{"Parameter", Text.Trim, type text}, {"Result", each Number.From(_, "sk-SK"), type number}}),
    GroupedRows = Table.Group(ChangedTypeAndTrim, {"Sample"}, {{"All", each _, type table}, {"Result", each 
        [ water = Table.SelectRows(_, (x)=> x[Parameter] = "Water"){0}?,
          sugar = Table.SelectRows(_, (x)=> x[Parameter] = "Sugar")[Result]{0}?,
          check = if water[Unity] = "aa" then water[Result] * sugar / 10 else null,
          result = Table.FromColumns(Table.ToColumns(_) & {{check}}, Value.Type(_ & #table(type table[Calculation=number],{})))
        ][result], type table}}),
    CombinedResult = Table.Combine(GroupedRows[Result])
in
    CombinedResult