Transform a column mulitplying where quotients depends by values of another column

[caseski]

Hello,

I must transform a column by mulptiplying. 

The problem is that I have two possible quotients depending by values of another column:

When ITEM contains CARB (it would be CARB005, CARB002.....) the quotient is 1,025. For example 2,88 x 1,025.

When ITEM doesn't contain CARB the quotient is 1,02. For example 3,67 x 1,02

[mahoneypat]

It is simpler to do that with an added column and then remove the original, but it is possible with a modified Replace Values step.  Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYMcjIwMFTSUTIEkrE60Up+/iEwASOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, pesa = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"pesa", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [pesa],each if Text.Contains([item], "CARB") then [pesa] * 1.025 else [pesa] * 1.02,Replacer.ReplaceValue,{"pesa"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"pesa", type number}})
in
    #"Changed Type1"

Table.TransformColumns - alternative in PowerBI and PowerQuery in Excel – The BIccountant

Pat

[caseski]

Thank you Pat, I know that it would be simpler with an added column but I must use the original as it is used in many measure and query.

I've tried with your code but it doesn't work, maybe I did something wrong. Consider that the table has many columns not only two. 

This is the result using your code:

[v-kkf-msft]

Hi @caseski ,

Try the following code:

#"Replace Value" = Table.ReplaceValue(#"Changed Type",each [pesa],each if Text.Contains([item], "CARB") then [pesa]*1025 else [pesa]*102, Replacer.ReplaceValue,{"pesa"})

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[caseski]

It doesn't work, the error is "EXPRESSION.ERRORE the name Changed Type is not recognized"

[v-kkf-msft · Accepted solution]

Hi @caseski ,

This expression #"Changed Type" is just a reference to the table generated in the previous step, you need to modify it to the last step of your original data table in the Advanced Editor. 

For example, in my sample data, the M code before replacing value is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MweSTq5+IYYGSrE60UpOQK6RhQWQdHYMcjIwMAWLOoOUWhhClRoYWrohhA0M4IqNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, pesa = _t, item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"pesa", Int64.Type}, {"item", type text}})
in
    #"Changed Type"

The M code before replacing value is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MweSTq5+IYYGSrE60UpOQK6RhQWQdHYMcjIwMAWLOoOUWhhClRoYWrohhA0M4IqNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, pesa = _t, item = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"pesa", Int64.Type}, {"item", type text}}),
    #"Replace Value" = Table.ReplaceValue(#"Changed Type",each [pesa],each if Text.Contains([item], "CARB") then [pesa]*1025 else [pesa]*102, Replacer.ReplaceValue,{"pesa"})
in
    #"Replace Value"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.