Complicated field to split or extract - NEED HELP!

[mrackley]

I have an important field from my server that I need to split multiple times to extract the data I need.

Example of the field from the dbo (all this data exists in one field just like this):

CaseID = 4633
Status [1.10 Receiving B/R In-House] -> [1.11 Subro/Balances Pending]
StatusNote [02/15 JF 4:6] -> [03/01 JF - Subro DONE (OHCA) | Balances #:#]
StatusDate [2/14/2022] -> [3/1/2022]
SubStatus1 [2.03 Liability Accepted] -> [2.11 Preparing for Demand]
SubStatus1Note [TPL Adjuster has accepted 100% liability] -> [03/01 JF Pending LW/Personal/Scar Photos]
SubStatus1Date [2/18/2022] -> [3/1/2022]
ts [2/18/2022 3:40:06 PM] -> [3/1/2022 10:45:36 AM]
LastModifiedDate [2/18/2022 3:40:06 PM] -> [3/1/2022 10:45:36 AM]

From this entire field, I only want the sections in red and underlined above to remain. What is contained in this field varies for each record based on activity, however "Status", "StatusNote", "StatusDate", etc. IF they were changed in the record, will be reflected in this field. Is there a way I can say -- If it finds "SubStatus1" in the field, tell me what's after "->"  AFTER "SubStatus1" -- how would I accomplish that?

OR how can I extract an entire line from this field?

Somehow extract "SubStatus1 [2.03 Liability Accepted] -> [2.11 Preparing for Demand]" and then I can extract further from there to get it down to just 2.11 Preparing for Demand.

We have a complicated SQL query that was written that does this for us, but its complicated and takes about 2-5 minutes to update and often crashes. Really do NOT want to link to a spreadsheet with a query to manually update each time before I can update PBI dashboards. Hoping to find a better way. 
Thank you!

[v-shex-msft · Accepted solution]

HI @mrackley,

I think you can try to use the nested m query functions to achieve your requirement. You can split the text to list based on '->' characters, then you can use text.contains function to check if they include any keywords and return results.

#"Added Custom" = Table.ExpandListColumn(Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.Split([Desc],"->"),each Text.Contains(_,"Subro/Balances Pending") or Text.Contains(_,"Preparing for Demand"))), "Custom")

Result:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldDRasIwFAbgVzlUBhuoSZpaRu/q3HCjatHBLlwvYnucGV0jSToY7OHXWidVFLbbcP7v5PzLpeP5nDtdZ2GFLQ0sWZ9RmGOK8lMWbzAkc3gsemNVGkyg91pSynE3xWBRrrQiQ5GLIkUDMRZZFUmcpHuiTpWtMtQlbABPD+AFfouinFBWP/caEEaz6T1cz8Z34Q18w4HvBJ0z9EjUdCV7xKWu23I5Yc3TUahcNTlWhfqUQyTFSubSfkGYpri1mLUIt74y1rgVuu5irTSM8EMU2QWzufM5jiDM3ktjUcNGGBB7GRilV5D/bjzXwb5DiF5IjNqoQuRkkQoN8UZZZS7sPZRw+6cSrGkNAw88GlAf4smZXPXlwBsE3IdwcmREwtiJyuRaYnay/l9i8gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Desc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Desc", type text}}),
    #"Added Custom" = Table.ExpandListColumn(Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.Split([Desc],"->"),each Text.Contains(_,"Subro/Balances Pending") or Text.Contains(_,"Preparing for Demand"))), "Custom")
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[HotChilli]

You could split the column by delimiter (using  -> ) and then filter the first column (from the column dropdown) using Text filter ,  "begins with" SubStatus1 or Status    ( with a space at the end of each)