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 | 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.
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
10 Replies
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 CombinedResultHi 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
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?
Does Table4 refer to this query?
