Extracting Text from various places within a string

[mshamsiev]

Hi all,

I have a column containing Priorities 1-4 in the format P1,P2 etc. which I'd like to isolate/extract.  

However, within the column, the entire string appears in various forms as seen below. I have attempted to use an if formula which has worked in extracting the 'P1' 'P2' format. However, how can I extract the 'Px' from the bottom format (TCS (SAP) etc.) THANKS!

SLA Definition

P4 - Incident

P2 - Incident

P1 - Incident

TCS (SAP) Resolve P4 SLA - Incident 

etc. 

[v-chuncz-msft]

@mshamsiev,

You could also try DAX function FIND and SEARCH.

[mshamsiev]

@v-chuncz-msft,

Hey again 🙂 Grateful for all the help mate! 

[MarcelBeug]

If the priority is always followed by a <blank>, this will work:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Priority", (This) => Text.Start(List.SingleOrDefault(List.Select({"P1 ","P2 ", "P3 ", "P4 "}, each Text.PositionOf(This[SLA Definition],_)>=0),""),2))
in
    #"Added Custom"

List.SingleOrDefault generates an error if >1 priority was found and it will return "" if no priority was found.

Edit: or even better:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Priority", (This) => Text.Start(List.SingleOrDefault(List.Select({"P1 ","P2 ", "P3 ", "P4 "}, each Text.Contains(This[SLA Definition],_)),""),2))
in
    #"Added Custom"

[mshamsiev]

Hi @MarcelBeug,

Thanks for assisting mate! Does this formula apply to Power BI? 

Cheers,

Mike 

[MarcelBeug · Accepted solution]

Yes, it is Power BI, more specifically Power Query, accessible via "Edit Queries".

Even simplified further:

let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Priority", (This) => List.SingleOrDefault(List.Select({"P1","P2", "P3", "P4"}, each Text.Contains(This[SLA Definition],_)),""))
in
    #"Added Custom"

[mshamsiev]

@MarcelBeug, you sir are a true gentleman! Thanks again!