Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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
AddedCustom
With this, can you see where I'm going wrong?
Does Table4 refer to this query?
It's the name of my table.
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.
Hmm... Yeah. Maybe. I can't say. I don't know much about it, unfortunately.
If so, what can I do to reverse it?
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.
Thank you for your help!
I've already done it. Sorry I'm such a newbie.
Glad that worked out!
👍