Row wise calculation in power query

[VSrujana]

I have a table with two columns Attribute and Value

Attribute	Value
A	3000
B	null

I would like to replace the null value with 30% of the corresponding value of A.

I do not want to pivot the information , as it is a lot of data and tranformations already running in the query.

I am looking for a solution/logic to calculate at a row level. 

Thanks.

[v-yingjl]

Hi @VSrujana ,

If the table just like your shows, you can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MDBQitWJVnICcpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New", each if [Value] = null then List.Max(#"Changed Type"[Value]) * 0.3 else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"New", Int64.Type}})
in
    #"Changed Type1"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

your example is really minimalist (at the very least, to get a meaningful answer, you should better explain what kind of "correspondence" there is between A and B. ).

In any case, the attached proposal works for a very large database and on the assumption that the nulls (which can be even more than 1) to be filled with the percentages are all consecutive and following the number available

[mahoneypat]

It isn't clear if you will alway have a number followed by a null (not multiple nulls).  If not, this is one way to do it.  If so, please provide more data/description.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MDBQitWJVnICcpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute", type text}, {"Value", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ReplacedWith30Pct", each if [Value] = null then 0.3 * #"Added Index"{[Index]-1}[Value] else [Value], type number)
in
    #"Added Custom"

Pat