Forum Discussion
Split by Delimiter - with Multiple Conditions
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.
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 protected] | Bobs Bins Survey Code 111 - Reference Number 794 JX88 533 | You don't often get email from [email protected]. Learn why this is Company Name Phone 079 7532 5554 |
| 2022-05-04T07:07:40+00:00 | [email protected] | PDF Survey request | You don't often get email from [email protected]. Learn why this is Surve 111 Company Name Phone 079 6543 5676 |
| 2022-05-04T07:07:40+00:00 | [email protected] | Reference No: 882XZ03889 | You don't often get email from [email protected]. Learn why this is Company Name Phone 079 6543 5676 |
