If Column contains ANY 3 letters and a dash (ABC-, XYZ- etc) then "OKAY", else "ERROR"

[Franny_Spanny]

Hi all, 

I've spent hours searching for an answer, and I have some ideas but nothing coming to fruition! Unfortunately I'm unable to share data, but my column looks like this:

NAME

1110009988

88888-GGGG-DDDD-

AGL-0001

2344778678

5278-837HJ

DHL-88747474

BIG-XX87655

JIB-129499

37HHU7733

I am trying to create a custom column that looks at my list, and anything that begins with ANY 3 letters plus a dash (???-) gets an "OKAY" but any other format gets "ERROR". 

I have tried IF StartsWith, IF Text.Contains, conditional columns using 'contains' and 'does not contain' but I'm just getting errors for everything. I've seen conflicting info on whether Wildcards work in PowerQuery, for example, the below formula gave me all errors:

if Text.Contains("***-", [Name]) then "OKAY" else "ERROR")

I have stumbled upon the use of R here but being on a work laptop I can't install anything without our IT and they can take a long time to respond so thought I'd ask the community for ideas.

Sorry for lack of fancy formatting, I'm new to the forum and not sure how it all works, but hopefully the above explains what I'm looking to do.

[rohit_singh · Accepted solution]

Hi @Franny_Spanny ,

This does not meet your requirements 100% but you could try something like this :

Check if the 4th position has a '-' and if yes then assign 'Okay' else 'Error'

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Middle([NAME], 3, 1) = "-" then "Okay" else "Error")

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 

[rsbin · Accepted solution]

@Franny_Spanny ,

I believe I have worked something out using DAX instead of PQ.
May not be the cleanest solution, but I believe it accounts for all of your scenarios:

NAME_TEST = SWITCH(
                TRUE(),
                MID( [Name], 4, 1 ) <> "-", "ERROR",
                MID( [NAME], 3, 1) IN  { "1","2","3","4","5","6","7","8","9","0" }, "ERROR",
                MID( [NAME], 2, 1) IN  { "1","2","3","4","5","6","7","8","9","0" }, "ERROR",
                LEFT( [NAME] , 1 ) IN { "1","2","3","4","5","6","7","8","9","0" }, "ERROR",
                 "OKAY" )

NAMENAME_TEST

1110009988	ERROR
88888-GGGG-DDDD-	ERROR
AGL-0001	OKAY
2344778678	ERROR
5278-837HJ	ERROR
DHL-88747474	OKAY
BIG-XX87655	OKAY
JIB-129499	OKAY
37HHU7733	ERROR

I haven't checked Rohit's solution, but feel free to work with whichever one is best for you.

[rohit_singh · Accepted solution]

Hi @Franny_Spanny ,

This does not meet your requirements 100% but you could try something like this :

Check if the 4th position has a '-' and if yes then assign 'Okay' else 'Error'

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Middle([NAME], 3, 1) = "-" then "Okay" else "Error")

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 

[Franny_Spanny]

Thanks @rohit_singh - this worked well! I found some erroneous data in the column so had to do some other fixing but this was perfect for the original question!