Forum Discussion

RhysWhite's avatar
RhysWhite
Frequent Visitor
4 years ago
Solved

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 ref...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

v-kkf-msft's avatar
v-kkf-msft
Community Support
4 years ago

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's avatar
RhysWhite
Frequent Visitor
4 years ago

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's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.