Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Franny_Spanny
Frequent Visitor

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

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.

 

2 ACCEPTED SOLUTIONS
rohit_singh
Solution Sage
Solution Sage

Hi @Franny_Spanny ,

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

rohit_singh_0-1661435341429.png

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! 

 

View solution in original post

rsbin
Super User
Super User

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

View solution in original post

3 REPLIES 3
rsbin
Super User
Super User

@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
Solution Sage
Solution Sage

Hi @Franny_Spanny ,

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

rohit_singh_0-1661435341429.png

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! 

 

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! 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.