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

[cgkas]

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.

Column1	Column2	Column3
1	5,9,774,2,0,22,34,13344,3	Numbers
2	John,Mary	John,Mary
3		No value
4	585,20	Numbers
5	Paul,Henry,Joan	Paul,Henry,Joan
6	880	Numbers
7	June,December	June,December
8	Apple,Grape,Orange	Apple,Grape,Orange
9		No value
10	771,3	Numbers

Thanks in advance for any help.

[Nathaniel_C]

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

[cgkas]

@Nathaniel_C 

Hi Nathaniel,

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

[Nathaniel_C]

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

[cgkas]

@Nathaniel_C Thanks so much for your help anyway! Quote

[Nathaniel_C]

You are welcome!

[MFelix]

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]

Hi @MFelix ,

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

Thanks, though,

Nathaniel

[MFelix]

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]

@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]

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

[cgkas]

---

@MFelix wrote:

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

---

@MFelix  I click on first row that has the value "Table" after your code and expands all values correctly, except 880 that remains in "Error" and says this:

Expression.Error: We cannot convert the value 880 to type Text.
Details:
Value=880
Type=Type

[MFelix · Accepted solution]

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

[cgkas]

@MFelix 

Thanks so much for your help.

I was getting error because I had missing the following step:

 #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Column2"}),

Now it works just fine.