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
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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
        Icon for Super User rankSuper 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.