CONTAINSSTRING with multiple Values -> Cut String

[lboldrino]

Hi 🙂

I have my Workdesciptions from sapByD and i mus find the jira bookinf in this column.

WorkDescription

AX2012-5703 Parameter für PriceEngine

AX2012-5703 Parameter für PriceEngine

AX2012-5711 Montageflag pro Artikelposition übernehmen und als Artikeltext andrucken

Teams-Meeting Jira AX2012-5466 [POQ] Anbindung Produktverfügbarkeit an AX

Nachbearbeitung Teams-Meeting "[POQ] Anbindung Produktverfügbarkeit an AX" und Abstimmung mit Thomas Schlick (AX2012-5466)

AX2012-5775 ESKOLE Aufträge als solche an übermitteln

AX2012-6056 Adresscode "Direkt" bzgl Dropshipping Bestellungen müssen an übergeben werden

AX2012-6177

Itsdone Wochenplanung + LuK Team Jour Fixe

LUKART-851: code change

PORTAL-469: database script execution on PROD

PORTAL-460: meeting

LUKART-851: code change

PORTAL-467: answer to supplier

PAC-117: database check + code check + database script on test

Itsdone Wochenplanung + LuK Team Jour Fixe

DWH-282: analysis

i will to have this Result :

WorkDescription	Jira Booking
AX2012-5703 Parameter für PriceEngine	AX2012-5703
AX2012-5703 Parameter für PriceEngine	AX2012-5703
AX2012-5711 Montageflag pro Artikelposition übernehmen und als Artikeltext andrucken	AX2012-5711
Teams-Meeting Jira AX2012-5466 [POQ] Anbindung Produktverfügbarkeit an AX	AX2012-5466
Nachbearbeitung Teams-Meeting "[POQ] Anbindung Produktverfügbarkeit an AX" und Abstimmung mit Thomas Schlick (AX2012-5466)	AX2012-5466
AX2012-5775 ESKOLE Aufträge als solche an übermitteln	AX2012-5775
AX2012-6056 Adresscode "Direkt" bzgl Dropshipping Bestellungen müssen an übergeben werden	AX2012-6056
AX2012-6177	AX2012-6177
Itsdone Wochenplanung + LuK Team Jour Fixe
LUKART-851: code change	LUKART-851
PORTAL-469: database script execution on PROD	PORTAL-469
PORTAL-460: meeting	PORTAL-460
LUKART-851: code change	LUKART-851
PORTAL-467: answer to supplier	PORTAL-467
PAC-117: database check + code check + database script on test	PAC-117
Itsdone Wochenplanung + LuK Team Jour Fixe
DWH-282: analysis	DWH-282

Note:

we have 12 BookingCode:

Jira Booking = if (CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"AX2012-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"LUKSALE-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"LUKMC-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"ISU-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"BIZ-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"DWH-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"AX40-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"DWHOPT-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"LUKART-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"PORTAL-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"LUKSM-")

|| CONTAINSSTRING(V4_WorkItemsLookup[WorkDescription],"PAC-"),??????????)

any idee?

ThanX 🙂

[Greg_Deckler · Accepted solution]

@lboldrino Try this with a few more CONTAINSSTRING's:

Column = 
    SWITCH(TRUE(),
        CONTAINSSTRING([WorkDescription],"AX2012-"),
                VAR __Start = SEARCH("AX2012-",[WorkDescription])
                VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))
                VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))
            RETURN
                MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),
        CONTAINSSTRING([WorkDescription],"LUKART-"),
                VAR __Start = SEARCH("LUKART-",[WorkDescription])
                VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))
                VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))
            RETURN
                MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),
        BLANK()
    )

[lboldrino · Accepted solution]

#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Jira", each if Text.Contains([WorkDescription], "AX2012-") then "AX2012-" else if Text.Contains([WorkDescription], "LUKSALE-") then "LUKSALE-" else if Text.Contains([WorkDescription], "LUKMC-") then "LUKMC-" else if Text.Contains([WorkDescription], "ISU-") then "ISU-" else if Text.Contains([WorkDescription], "BIZ-") then "BIZ-" else if Text.Contains([WorkDescription], "DWH-") then "DWH-" else if Text.Contains([WorkDescription], "AX40-") then "AX40-" else if Text.Contains([WorkDescription], "DWHOPT-") then "DWHOPT-" else if Text.Contains([WorkDescription], "PORTAL-") then "PORTAL-" else if Text.Contains([WorkDescription], "LUKSM-") then "LUKSM-" else if Text.Contains([WorkDescription], "LUKART-") then "LUKART-" else if Text.Contains([WorkDescription], "PAC-") then "PAC-" else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Code", each if [Jira] <> null then Text.Select(Text.BetweenDelimiters([WorkDescription],"-"," "),{"0".."9"}) else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Jira Booking", each if [Code] <> "" then [Jira] & [Code] else null)

[lboldrino]

@Greg_Deckler You are the Best! 🙂

[Greg_Deckler · Accepted solution]

@lboldrino Try this with a few more CONTAINSSTRING's:

Column = 
    SWITCH(TRUE(),
        CONTAINSSTRING([WorkDescription],"AX2012-"),
                VAR __Start = SEARCH("AX2012-",[WorkDescription])
                VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))
                VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))
            RETURN
                MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),
        CONTAINSSTRING([WorkDescription],"LUKART-"),
                VAR __Start = SEARCH("LUKART-",[WorkDescription])
                VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))
                VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))
            RETURN
                MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),
        BLANK()
    )

[lboldrino]

@Greg_Deckler 

[lboldrino]

@Greg_Deckler see my errores:

Desciption                                                                   your Jira                      Correct

Abstimmung DWH-282                                               DWH-28                     DWH-282 

Abstimmung mit HP bezüglich AX2012-5291             AX2012-529               AX2012-5291

Abstimmung Portal-261  Katharina   Huber                Portal-261 Katharina  Portal-261
Analyse AX2012-5260                                                  AX2012-526               AX2012-5260

what do you mean?

Jira Booking = SWITCH(TRUE(),

CONTAINSSTRING([WorkDescription],"AX2012-"),

VAR __Start = SEARCH("AX2012-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"LUKSALE-"),

VAR __Start = SEARCH("LUKSALE-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"LUKMC-"),

VAR __Start = SEARCH("LUKMC-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"ISU-"),

VAR __Start = SEARCH("ISU-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"BIZ-"),

VAR __Start = SEARCH("BIZ-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"DWH-"),

VAR __Start = SEARCH("DWH-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"AX40-"),

VAR __Start = SEARCH("AX40-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"DWHOPT-"),

VAR __Start = SEARCH("DWHOPT-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"PORTAL-"),

VAR __Start = SEARCH("PORTAL-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

CONTAINSSTRING([WorkDescription],"LUKSM-"),

VAR __Start = SEARCH("LUKSM-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),     

CONTAINSSTRING([WorkDescription],"LUKART-"),

VAR __Start = SEARCH("LUKART-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),     

CONTAINSSTRING([WorkDescription],"PAC-"),

VAR __Start = SEARCH("PAC-",[WorkDescription])

VAR __Colon = SEARCH(":",[WorkDescription],__Start,LEN([WorkDescription]))

VAR __Space = SEARCH(" ",[WorkDescription],__Start,LEN([WorkDescription]))

RETURN

MID([WorkDescription],__Start,MIN(__Colon, __Space) - __Start),

BLANK()

)

[Greg_Deckler]

@lboldrino Well, one problem is that it looks like you don't have a space or a colon after those so it is grabbing the entire next word. Not sure what to do with that one unless those always have a set number of characters like AX2012-xxxx would be 11 characters but if you had AX2012-xxx then you would get one too many characters.

The second issue is that anywhere you have a LEN([Workdescription) make it 

LEN([WorkDescription])+1

[lboldrino]

ok, 

for exm.: for  AX2012- i have only  AX2012-xxxx. is it usefull?