none
Replace all non-null values in a column with the MAX from the same column RRS feed

  • Question

  • Looking for pointers on how I can accomplish replacing all non-null values in a column with the maximum value found in the same column.

    For example, in the column below, I'd end up with "3" as the value for every row that doesn't contain a null value.


    Ian

    Saturday, November 11, 2017 6:45 PM

Answers

  • Eventually got there. Got a little simpler after I realized I could remove all the null rows because they wouldn't be useful later. 

    = Table.ReplaceValue(minDur,each [F2minDur], each List.Max(minDur[F2minDur]),Replacer.ReplaceValue,{"F2minDur"})

    This helped: https://community.powerbi.com/t5/Desktop/Query-editor-replacing-values-based-on-another-column/m-p/156994

    Probably would have been doable with trying to edit only non-null values, but again, in this case I would have ended up filtering them later, so I just moved that up a few steps where it better belonged.


    Ian

    • Marked as answer by Ian Bruckner Saturday, November 11, 2017 7:46 PM
    Saturday, November 11, 2017 7:46 PM

All replies

  • Eventually got there. Got a little simpler after I realized I could remove all the null rows because they wouldn't be useful later. 

    = Table.ReplaceValue(minDur,each [F2minDur], each List.Max(minDur[F2minDur]),Replacer.ReplaceValue,{"F2minDur"})

    This helped: https://community.powerbi.com/t5/Desktop/Query-editor-replacing-values-based-on-another-column/m-p/156994

    Probably would have been doable with trying to edit only non-null values, but again, in this case I would have ended up filtering them later, so I just moved that up a few steps where it better belonged.


    Ian

    • Marked as answer by Ian Bruckner Saturday, November 11, 2017 7:46 PM
    Saturday, November 11, 2017 7:46 PM
  • Hey!

    Perhaps this is what you're looking for. The video is 2 years old, but still quite relevant:

    Video


    • Edited by Miguel Escobar Saturday, November 11, 2017 9:16 PM the issue appears to be resolved
    Saturday, November 11, 2017 9:16 PM