Split by Delimiter - with Multiple Conditions

[RhysWhite]

I hope somebody can help me quickly,

I have eMail data coming into Excel via Power Automate and I need to extract a reference number from either the Subject Or Body of the eMail.

Most of the references are preceded by certain words such as Reference or Number.

The solution I have come up with is to use Split Column By Delimiter - how do I search for multiple delimiters if the last one wasn't present?

E.g If I split by Number and it finds Number it splits, if it doesn't find number it moves onto the next one which maybe Reference.

Is this correct?  This is the base 
= Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Subject", type text}}, "en-GB"), "Subject", Splitter.SplitTextByAnyDelimiter({"Number"}, QuoteStyle.Csv, true), {"Subject.1", "Subject.2"})

If I modify it like this with multiple conditions it still seems to miss some.
= Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Subject", type text}}, "en-GB"), "Subject", Splitter.SplitTextByAnyDelimiter({"Number", "Reference", "no."}, QuoteStyle.Csv, true), {"Subject.1", "Subject.2"})

I am unable to share any data / due to security 
Originally posted in the wrong group.

[RhysWhite]

@Greg_Deckler thanks, original post updated with example data - outcome would be a new colum with just the reference number in it.  Reference numbers follow no set format.

[v-kkf-msft]

Hi @RhysWhite ,

Please add the custom column in PQ, then expand the list to new rows.

Split Subject =
if Text.Contains([Subject], "Number") then Splitter.SplitTextByDelimiter("Number", QuoteStyle.Csv)([Subject]) else Splitter.SplitTextByDelimiter("Reference", QuoteStyle.Csv)([Subject])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RhysWhite]

Thank you, I get the following error - 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

[v-kkf-msft · Accepted solution]

Hi @RhysWhite ,

This is because your code is missing "each".

If you are changing code in the advanced editor, please add "each" before the original code. 

And the complete code is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZJPTwIxEMW/ygtX+dPt7tJlTwrGBEMIQQ8oy6HArLuRtth2MXx7F6JR9KLeuHTSyZu8mV/efN7gjPMWi1ssumciZd2UiwvGUsYazQYpWW4uj297ZVTd6ZulQ7/UDneV3dEeA7MmBEGAFqaUkyW9IowrtSQL0YtwO0sSxGFYzz6YCmujs4oxLjxM7knjiTyOBsitUfjm2MaIpNV4LfbwRelQukyXamusl9pjXni/dWmnI59lW7nOUXy1NJW/I70mO1yT9mVerqQvjV5kOtOTDUlHWEmNIQq5I0hMrm+wI+tqTb1VbUTIjVUH+VgqOtSBUVup9/j4TwqjCUz0IOKQI47jqLFo/qAp0oid0myf4Dw4v4O09FKR82fK6XjEIQeZ/oxBwEOMpjzg4CL4Hc1uHIWIu6L7H5pfAmhSJAmfPbIwSXpnyvRPtBZv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"From eMail" = _t, Subject = _t, Body = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"From eMail", type text}, {"Subject", type text}, {"Body", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Split Subject", each if Text.Contains([Subject], "Number") then Splitter.SplitTextByDelimiter("Number", QuoteStyle.Csv)([Subject]) else Splitter.SplitTextByDelimiter("Reference", QuoteStyle.Csv)([Subject])),
    #"Expanded Split Subject" = Table.ExpandListColumn(#"Added Custom", "Split Subject")
in
    #"Expanded Split Subject"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RhysWhite]

OK - Some More Info

eMails are ripped from Outlook to Excel using Power Automate, e mails fall into a table - a faked example of which can be seen below.  This query is currently called tbl_Unprocessed

There is another table of 120K references which could posisbly be in the subject line or body.  Originally I started by looking for ways to search the subject / body of each e mail for any of these references held in imp_Data - but that doesn't seem possible.

So began looking for patterns in the data and found most of the references are preceded by words such as Reference or Number.

Came up with the idea of spliting the table at these words but need it to operate on multiple words sequentially not at once.

Any ideas?

The table is emails ripped from outlook using power automate - the body is text only.

Date	From eMail	Subject	Body
2022-05-04T07:06:27+00:00	email@email.com	Bobs Bins Survey Code 111 - Reference Number 794 JX88 533	You don't often get email from email@email.com. Learn why this is important [https://aka.ms/LearnAboutSenderIdentification] Please can I have a PDF version of the form Name Company Name Phone 079 7532 5554
2022-05-04T07:07:40+00:00	email.@email.com	PDF Survey request	You don't often get email from email@email.com. Learn why this is important [https://aka.ms/LearnAboutSenderIdentification] Please can I have a PDF version of the form Surve 111 Reference 123 LR212 271 Name Company Name Phone 079 6543 5676
2022-05-04T07:07:40+00:00	email.@email.com	Reference No: 882XZ03889	You don't often get email from email@email.com. Learn why this is important [https://aka.ms/LearnAboutSenderIdentification] Please can I have a PDF version of the form Name Company Name Phone 079 6543 5676

[mussaenda]

hi @RhysWhite ,

Basically, you need to extract the Reference No?

I have a solution. It is not the best solution, but hope this might help you.

I also attached a sample pbix for you to test.

Hope this helps.

[mussaenda]

hi @RhysWhite ,

Basically, you need to extract the Reference No?

I have a solution. It is not the best solution, but hope this might help you.

I also attached a sample pbix for you to test.

Hope this helps.

[Greg_Deckler]

@RhysWhite Just share mocked-up data the replicates the problem. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.