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

AttributeValue
A3000
Bnull


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.

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.