Forum Discussion

acorr's avatar
acorr
Frequent Visitor
2 years ago

Isolate true values from null in cell that contains both

Hi there, I am working with a column that has data that looks like the below ,each row is a cell. How can I set this column up to present the highest number if there's an existing number only, and a null only if there is no number? 

 

 

 

 

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Convert your strings into lists. Filter the list to exclude "NULL" .  Convert data type to Int64. Grab List.Max.  

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    In Power Query Advanced Editor:

     

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Max Value", each 
        List.Max(
            List.ReplaceValue(
                Text.Split([Column1],","),
                "NULL",
                null,Replacer.ReplaceValue)
                )
            )
    • acorr's avatar
      acorr
      Frequent Visitor
      2 years ago

      Thank you - I am having trouble with adding this; I get the token eof error which I can't seem to resolve.) I don't have much experience with power query but I updated the let in statement to match the formatting to how it was previously (and replaced [Column1], which doesn't appear to be the issue) - any chance you are able to help troubleshoot? 

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        2 years ago

        It's tough to troubleshoot for you when you chose to not supply your code or a data sample as text, but perhaps you could work from the Power Query UI instead.

         

        • Be sure to set the relevant column datatype to Text
        • Add Column
          • Custom Column

         

        • Enter this formula in the formula box:
        List.Max(List.Transform(
            List.ReplaceValue(
                Text.Split([Column1],","),
                "NULL",
                null,Replacer.ReplaceValue), each Number.From(_)))

         

        Replace [Column1] with whatever your actual column name is.