How to extract text patterns from a summary / sentence ?

[Anonymous]

Hi

I have a sentence in a 'Summary"cell like,

Sl No	Summary	Closed	Ticket Num
1	Your ticket REQ000111 is pending	No
2	Your issue LMPT0000011 is ready to close	Yes

I need to find the pattern REQ & LMPT and extract REQ000111 and LMPT0000011, then write these values to 'Ticket Num' column.

Length of a REQ# number is 9 and LMPT# is 11. Each rows can have either REQ or LMPT mentioned somehwere in summary.

Expectation is that column 'Ticket number' should display the full ticket numbers like REQXXXXXX or LMPTXXXXXXX.

[Anonymous · Accepted solution]

Here is a way you can do it in Power Query.  I've made the following assumptions:

• REQ always have 6 numbers, to make it 9 characters long
• LMPT always has 7 numbers, to make it 11 characters long

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcqxCoAgEADQXzludjj7h7aKipYQh6gjpNDwdOjvy6A3P2NQo8I55AjJrQcnGOuBiLTW4AQu9pvz+1u6gFYZrP7tRDJD0/YT0fdLj7xsN6QA6xmES2VBax8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Sl No" = _t, Summary = _t, Closed = _t]),
    #"Added Summary Length" = Table.AddColumn(Source, "Summary Length", each Text.Length([Summary]), Int64.Type),
    #"Added Ticket Num" = Table.AddColumn(#"Added Summary Length", "Ticket Num", each if Text.PositionOf([Summary], "REQ") = -1 then 
	Text.Start(Text.End([Summary], [Summary Length] - Text.PositionOf([Summary], "LMPT")), 11) else 
	Text.Start(Text.End([Summary], [Summary Length] - Text.PositionOf([Summary], "REQ")), 9), type text)
in
    #"Added Ticket Num"

[Anonymous]

I copied this code into another data sheet ( to find request and ticket)  and chnaged the qyery to match the new search items.

ONce I click done from the advanced editor,  still  loaed same data I attached above. Its not showing the data in new sheet (new pbix for anohter file)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcqxCoAgEADQXzludjj7h7aKipYQh6gjpNDwdOjvy6A3P2NQo8I55AjJrQcnGOuBiLTW4AQu9pvz+1u6gFYZrP7tRDJD0/YT0fdLj7xsN6QA6xmES2VBax8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Issue key" = _t, Summary = _t, Assignee = _t]),
#"Added Summary Length" = Table.AddColumn(Source, "Summary Length", each Text.Length([Summary]), Int64.Type),
#"Added Ticket Num" = Table.AddColumn(#"Added Summary Length", "Ticket Num", each if Text.PositionOf([Summary], "REQ") = -1 then
Text.Start(Text.End([Summary], [Summary Length] - Text.PositionOf([Summary], "REQUEST")), 13) else
Text.Start(Text.End([Summary], [Summary Length] - Text.PositionOf([Summary], "TICKET")), 10), type text)
in
#"Added Ticket Num"

[Anonymous]

The source row in my code uses the "Enter Data" to create a temporary table.  What you need to do is to understand what the code does and apply it to your own source row.  You'll likely need to update parts of the code to connect into whatever your existing power query code is.

The code could be cleaned up a little too, but its more important you get the general idea of what its doing.

[Anonymous]

OOh yes!! I got it. 

Why did I pasted source also , thanks for reminding.

[Anonymous]

omg!!! that worked!!

Thank you so much!!!!