IF with ISNUMBER and number of digit criteria

[jboschee34]

Hi everyone,

I'm looking at data in 3 different columns.  The product number I need could be in any column.  The columns currently have junk with things like cities and upc numbers in them where the product number is not.  The product numbers are also only 5-6 digits long.  Is there a way to write a ISNUMBER dax formula in a new columa to isolate the product number from the citiies and a count of the digits to make sure only ones with 5-6 digits come back as True?

Thanks for the help!

[parry2k]

@jboschee34 in PQ try to add another custom column and copy this expression. Change column names as per your table:

try List.RemoveItems(List.Transform({[Reference 1],[Reference 2],[Reference 3]},(x as any) => 
let
length = if x is null then null else Text.Length(x),
value = try Number.From(x) otherwise null, 
value1 = if length = 5 or length =6 then value else null 
in value1
),{null}){0} otherwise null

[parry2k]

@jboschee34 what is the ask here? The product number can be any of these 3 columns with the original rules you defined. Is this what you are looking for?

[jboschee34]

@parry2k  Correct.  The product number can live in any of the 3 columns, but will never contains any letters or characters such as dashes.  Also, the product number will either be 5 or 6 digits long.

[parry2k]

@jboschee34 if you want a value instead of true/false, change to this:

if (try Number.FromText([Column1]) otherwise null) = null then null else if Text.Length([Column1]) = 5 or Text.Length([Column1]) = 6 then [Column1] else null

[jboschee34]

I got all null results

[vanessafvg]

you will probably need to share some data to understand what it looks like.  hard to a code a solution without fully understanding the requirements.

[jboschee34]

Here's some example data @parry2k 

Reference 1	Reference 2	Reference 3
1570027-15	154505
1489920	152582
Furniture	SO674111	146761
Furniture	SO674111	146761
Furniture	SO674111	146761
Furniture	SO677170	147904
Furniture	SO677170	147904
Furniture	SO677170	147904
Furniture	SO677170	147904
155237

[jboschee34]

Most of the time, the right data will be in the Reference 1 column but I provided some of the problem childs.

[parry2k]

@vanessafvg good solution but how do you find out if this is a valid number, I think that is one of the rules along with length check. 

ABCDEF = 6 but not a product because it is not a number

123456 = 6, it is a number and meet the length criteria 

🙂

[vanessafvg]

thanks parry, pure slip of the memory, i forgot to include that !

here is another version.. @parry2k  didn't mean to double post, i think we responded at the same time howver, didn't see your response before i responded.

a million ways (well not quite) to do this

productno =
VAR isno =
IF ( ISNUMBER ( field ), 1 )
VAR islen =
IF ( OR ( LEN ( [field] ) = 5, LEN ( [field] ) = 6 ), 1 )
RETURN
IF ( isno + islen = 2, field, BLANK () )

[parry2k · Accepted solution]

@vanessafvg take it easy! we all here to assist.

@jboschee34 if for some reason you cannot use PQ (I would recommend that, my previous solution), you can use the following DAX expression, add it as a column

Product Number = 
VAR __Table = { MyProduct[Reference 1], MyProduct[Reference 2], MyProduct[Reference 3] } 
VAR __AddRules = ADDCOLUMNS ( __Table, "@Length", LEN ( [Value] ), "@IsNumber", NOT ISERROR ( CONVERT ( [Value], INTEGER ) ) )
RETURN
MAXX ( FILTER ( __AddRules , [@Length] IN { 5, 6 } && [@IsNumber] ), [Value] )

[jboschee34]

@parry2k Thanks!  I tried to get the PQ expression to work but kept getting null values.  I'm not very experienced in the PQ side of things.  The DAX expression worked perfectly though.  I need to definitely get more familiar with the PQ.  Thanks to you and @vanessafvg for all the help!

[vanessafvg]

@parry2k  all easy here 🙂  just sayin... 

@jboschee34  did you attempt the  new solution i posted if you can't get the other to work?  but do think pushing the calculation into power query is a a better practice. 

[jboschee34]

ahhh nuts.  good point @parry2k.  could I put an IF(ISNUMBER  before it?

[vanessafvg]

this might work?

productno =
IF ( OR ( LEN ( [field] ) = 5, LEN ( [field] ) = 6 ), field, BLANK () )

[jboschee34]

perfect!  Thank you!

[parry2k]

@jboschee34 in PQ add a column to mark it true or false

if (try Number.FromText([Column1]) otherwise null) = null then false else if Text.Length([Column1]) = 5 or Text.Length([Column1]) = 6 then true else false

[jboschee34]

I got all False results.  Do they need to start in a certain format?