Forum Discussion
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...
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 AddedCustomor 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 Combinewith this result
I hope this is helpful
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
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 AddedCustomWith this, can you see where I'm going wrong?
