Extract strings after a search

[rmbmw]

I have a power query that imports some emails into a specific folder. Now In the text body, there is some specific information I want to extract to a column. For example, a text contains "Duration:" After that, the duration is random. So I want to let's say "Duration" plus the specific text 24 strings. What is after that specific text? the "duration" text is unique but is not static meaning is no always in the same location of the text

The column will equal Duration: 0 Hours 24 Minutes 25 Seconds, for example, and so on.

 

[gargamalebarbosa]

like that ?

 

[rmbmw]

Yes, but remember, the duration is not always the same and is not always in the exact location. So I cannot split it by delimiter, it needs to be something more dynamic

 

[gargamalebarbosa]

Yes, but remember, the duration is not always the same and is not always in the exact location. So I cannot split it by delimiter, it needs to be something more dynamic

Could you share example with all posibilty

 

[rmbmw]

There are only two possibilities. Here it is. I removed actual information, but you'll get the idea. as you can see, sometimes it comes with the location

Example 1:

Ticket Number: (Ticket Number here)

Issue: Issue goes here

Start Time: 08/21/22 23:15:04

Location: location goes here

Duration: 16 hours 21 minutes 53 seconds

Call Volume: 0

Incident Owner: Incident owner

Incident Manager: manager

Root Cause: root cause

Associated Maintenance: associated maintenance


Example 2:


Ticket Number: (Ticket Number here)

Issue: Issue goes here

Start Time: 08/21/22 16:29:12

Duration: 23 hours 4 minutes 44 seconds

Call Volume: 0

Incident Owner: Incident owner

Incident Manager: manager

Root Cause: root cause

Associated Maintenance: associated maintenance

 

[gargamalebarbosa]

is this what you want ?

 

[rmbmw]

The way you are putting it is easy; that text is part of only one cell, I'm trying to extract it individually

 

[alansidman]

Can you post your sample using XL2BB as we cannot manipulate data in a picture. Provide several samples as you indicated that the duration is not always in the same location. 5-10 records should suffice.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl  = Table.TransformColumns(Source, {{"Column1", each Text.Split(_, Character.FromNumber(10))}}),
    tbl1 = Table.TransformColumns(tbl, {{"Column1", each Text.Replace(List.Select(_, (x)=> Text.StartsWith(x,"Duration:")){0}, "Duration: ", "")}})
in 
    tbl1

or

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    tbl  = Table.TransformColumns(Source, {{"Column1", each Text.Split(_, Character.FromNumber(10))}}),
    tbl1 = Table.TransformColumns(tbl, {{"Column1", each Text.Replace(List.Select(_, (x)=> Text.StartsWith(x,"Duration:")){0}, "Duration: ", "")}}),
    tbl2 = Table.SplitColumn(tbl1, "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Hours", "Column1.2", "Minutes", "Column1.4", "Seconds", "Column1.6"}),
    tbl3 = Table.RemoveColumns(tbl2,{"Column1.2", "Column1.4", "Column1.6"})
in 
    tbl3

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2		A	Ticket Number: (Ticket Number here) Issue: Issue goes here Start Time: 08/21/22 23:15:04 Location: location goes here Duration: 16 hours 21 minutes 53 seconds Call Volume: 0 Incident Owner: Incident owner Incident Manager: manager Root Cause: root cause Associated Maintenance: associated maintenance
3		B	Example 2: Ticket Number: (Ticket Number here) Issue: Issue goes here Start Time: 08/21/22 16:29:12 Duration: 23 hours 4 minutes 44 seconds Call Volume: 0 Incident Owner: Incident owner Incident Manager: manager Root Cause: root cause Associated Maintenance: associated maintenance
4
5					Item	Column1		Item	Hours	Minutes	Seconds
6					A	16 hours 21 minutes 53 seconds		A	16	21	53
7					B	23 hours 4 minutes 44 seconds		B	23	4	44
8
Sheet4