Forum Discussion

VSrujana's avatar
VSrujana
Frequent Visitor
5 years ago
Solved

Row wise calculation in power query

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 wan...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
mahoneypat
Microsoft Employee
5 years ago

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