Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
6 years ago
Solved

How to check if column contains numbers an null values PQuery?

Hello to all,

 

May someone help me with this. I haven't found a function to do this. I have a table with Column2 with type formatted as text (ABC) containing the data shown below. I'd like to have a 3rd Column (Column3) that shows the text "Numbers" when there are only numbers separated by comma in Column2, show the value in Column2 if Column2 not contains numbers and show "Not value" if Column2 = Null/Empty.

 

Column1Column2Column3
15,9,774,2,0,22,34,13344,3Numbers
2John,MaryJohn,Mary
3 No value
4585,20Numbers
5Paul,Henry,JoanPaul,Henry,Joan
6880Numbers
7June,DecemberJune,December
8Apple,Grape,OrangeApple,Grape,Orange
9 No value
10771,3Numbers

 

Thanks in advance for any help.

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi cgkas ,

     

    Don't understand why you say expand values after table?

     

    You should add a calculated column, check PBIX file attach.

     

    Also the DAX expression I have place on this post included.

     

    Regards,

    MFelix

13 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi cgkas ,
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    Which = 

var _C1 = LEFT('Status'[Column2],1)

var _isnumber =IF('Status'[Column2]="","No Value",IF(_C1 in {"1","2","3","4","5","6","7","8","9","0"},"Numbers", 'Status'[Column2]))
return _isnumber

     

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi Nathaniel_C ,

       

      Don't want to rain on your parade but  believe that this will not get the correct result, what if the cell values is for example "1, ABC, 2" as you are only checking the first character would return number instead of text.

       

      I know that the value is not on the example but is on the question "when there are only numbers separated by comma in Column2".

       

      Using dax should probably use this calculated column:

      Column =
IF (
    'Table'[Column2] = "";
    "No Values";
    IF (
        IFERROR (
            FORMAT ( SUBSTITUTE ( 'Table'[Column2]; "."; "" ); "###" ) + 0;
            BLANK ()
        )
            = BLANK ();
        'Table'[Column2];
        "Numbers"
    )
)

      Sorry for this correction

       

      Regards,

      MFelix

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi MFelix ,

        "show the value in Column2 if Column2 not contains numbers." is what I read. 

        Thanks, though,

        Nathaniel

    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V
      6 years ago

      Nathaniel_C 

      Hi Nathaniel,

       

      Where should I insert your code? in Add custom Column? or how?

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi cgkas ,
        I entered it as a calculated column.  However MFelix  is correct in that Power Query is a more efficient way to handle this.

        Nathaniel



         

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi cgkas ,

     

    In the query editor add a calculated column with the following code:

     

    = Table.AddColumn(#"Replaced Value", "Custom", each if Value.Is(Value.FromText(
    Text.Remove([Column2],{","}) ), type number) then "Numbers" else if [Column2] = null then "No Value" else [Column2])

    Regards,

    MFelix

    • cgkas's avatar
      cgkas
      Icon for Helper V rankHelper V
      6 years ago

      MFelix 

       

      Hi Felix,

       

      Thanks for answer and your help. It almost work fine, I get error in the line where Column2= 880.

       

      How to fix this?

       

      Thanks again

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi cgkas ,

         

        What is the error you are getting?

         

        Click on the cell where there is the error (not on the Error word) and check on the bottom what is the text for the error please.

         

        On my file everything went ok.

         

        Regards,

        MFelix