Isolate true values from null in cell that contains both

[acorr]

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? 

[ronrsnfld]

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]

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]

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.

[acorr]

Thanks so much for your help. The custom way produces "Error" in the field (I did convert to text formula.) Here's what I've got now :

[ronrsnfld]

The functions work here using the information that you presented. Which seems to be merely a screenshot of a selection dropdown.

Unless you can provide sufficient data for me to reproduce your problem, I will not be able to help you troubleshoot.

Most likely, the data you presented is not representative of your actual data, but there may be other issues also.

[lbendlin]

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