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
lboldrino
Resolver I
Resolver I

CONTAINSSTRING with multiple Values -> Cut String

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 :

WorkDescriptionJira Booking
AX2012-5703 Parameter für PriceEngineAX2012-5703
AX2012-5703 Parameter für PriceEngineAX2012-5703
AX2012-5711 Montageflag pro Artikelposition übernehmen und als Artikeltext andruckenAX2012-5711
Teams-Meeting Jira AX2012-5466 [POQ] Anbindung Produktverfügbarkeit an AXAX2012-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 übermittelnAX2012-5775
AX2012-6056 Adresscode "Direkt" bzgl Dropshipping Bestellungen müssen an übergeben werdenAX2012-6056
AX2012-6177AX2012-6177
Itsdone Wochenplanung + LuK Team Jour Fixe 
LUKART-851: code changeLUKART-851
PORTAL-469: database script execution on PRODPORTAL-469
PORTAL-460: meetingPORTAL-460
LUKART-851: code changeLUKART-851
PORTAL-467: answer to supplierPORTAL-467
PAC-117: database check + code check + database script on testPAC-117
Itsdone Wochenplanung + LuK Team Jour Fixe 
DWH-282: analysisDWH-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 🙂
 
 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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()
    )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

lboldrino
Resolver I
Resolver I

#"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)

View solution in original post

7 REPLIES 7
lboldrino
Resolver I
Resolver I

#"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
Resolver I
Resolver I

@Greg_Deckler You are the Best! 🙂

Greg_Deckler
Super User
Super User

@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()
    )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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()
)

 

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

ok, 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.