Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Here is a way you can do it in Power Query. I've made the following assumptions:
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"
Here is a way you can do it in Power Query. I've made the following assumptions:
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"
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"
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.
OOh yes!! I got it.
Why did I pasted source also , thanks for reminding.
omg!!! that worked!!
Thank you so much!!!!
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |