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 ...
Need Help
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.

acorr's avatar
acorr
Frequent Visitor
2 years ago

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's avatar
    ronrsnfld
    Super User
    2 years ago

    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.