Conditional calculation of new column in Power Query

[sara11]

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	Parameter	Result	Unity
A	Water	750	aa
A	Sugar	0,12	%
A	Chemical X	727,3	mg
A	Chemical Z	0,22	g
B	Water	4,93	mg
B	Sugar	5,28	%
B	Chemical X	5,05	mg
B	Chemical Z	300	g

If "Unity" of Water (Parameter column) = "aa", then:
(Water x Sugar)/10
in this example: (750 x 0.12)/10
If "Unity" of Water (Parameter column) = "mg", then:
do nothing

However, I have to make sure that the "Sample" is the same for Water and Sugar in order to carry out the multiplication.

I'm not being able to carry out this operation.
Thank you in advance for your help.

[dufoq3]

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

[m_dekorte · Accepted solution]

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

[sara11]

m_dekorte,

Thanks for your help, unfortunately I haven't been able to reproduce what you've given me. I've made some adaptations to my situation, but I get an error:

Expression.Error: A cyclic reference was encountered during evaluation.

let

Source = Table4,

ChType = Table.TransformColumnTypes(Source,{{"Result", type number}}, "nl-NL"),

AddedCustom = Table.AddColumn(ChType, "Custom", each [

t = Table.FindText( ChType, [Desc.Sample] ),

a = if [Parameter] = " Water" and [Units] = " aa"

then List.Product( Table.SelectRows(t, each [Parameter] =" Water " or [Parameter]=" Sugar")[Result]) /10

else [Result]

][a]

)

in

AddedCustom

With this, can you see where I'm going wrong?

[m_dekorte]

Does Table4 refer to this query?

[sara11]

It's the name of my table.

[m_dekorte]

Yes, I understand. 

However there doesn't appear to be any self referencing within the shared code. Therefore I am wondering if Table4 points to this query - the one for which you have shared the code - because that will generate:

Expression.Error: A cyclic reference was encountered during evaluation.

[sara11]

Hmm... Yeah. Maybe. I can't say. I don't know much about it, unfortunately.

If so, what can I do to reverse it?

[m_dekorte]

Look for the query name (for which you shared the code) inside Table4 's code - if that name includes a space, special character or keyword the quoted notation will be used. To illustrate, let's say it is called: my Query, because this includes a space it will show up as #"my Query" in the M code inside Table4 but without a space myQuery will show as myQuery

To resolve it you have to determine which comes first and restore the Source step in that query to access the data that you need to transform. 

Alternatively you may be able to combine the logic from these two queries into one, but that is only possible if you do not require them as separate tables elsewhere in your workflow.

[sara11]

Thank you for your help!

I've already done it. Sorry I'm such a newbie.

[m_dekorte]

Glad that worked out!

👍