Forum Discussion
Split by Delimiter - with Multiple Conditions
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.
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.
8 Replies
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.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
important [https://aka.ms/LearnAboutSenderIdentification]
Please can I have a PDF version of the form
NameCompany 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
important [https://aka.ms/LearnAboutSenderIdentification]
Please can I have a PDF version of the formSurve 111
Reference 123 LR212 271
NameCompany 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
important [https://aka.ms/LearnAboutSenderIdentification]
Please can I have a PDF version of the form
NameCompany Name
Phone 079 6543 5676
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.
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.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?
