Split Column into multiple Columns and Rows

[rodfernandez]

Hello,
I am trying to extract information from my email, using the exchange connector.

I need to extract the information that comes in the column "Body.Text", currently the information comes in the following structure

Subject	Date	Body.Text
Mail1	10-03-2021	ID NAME NUMBER TYPE 001 Rodrigo 1234 Type A 002 Joe 3853 Type C
Mail2	11-03-2021	ID NAME NUMBER TYPE 003 Jeff 9573 Type A
Mail3	06-02-2021	ID NAME NUMBER TYPE 004 Bob 2253 Type A 005 Andy 8862 Type B 006 Bart 3321 Type A

But I need to transform the data to something with this structure

Subject	Date	ID	NAME	NUMBER	TYPE
Mail1	10-03-2021	001	Rodrigo	1234	Type A
Mail1	10-03-2021	002	Joe	3853	Type C
Mail2	11-03-2021	003	Jeff	9573	Type A
Mail3	06-02-2021	004	Bob	2253	Type A
Mail3	06-02-2021	005	Andy	8862	Type B
Mail3	06-02-2021	006	Bart	3321	Type A

I have been trying to use the "Split Column" option but I have not been able to create the "Table 2"

I would be very grateful if you could support me with this problem

Thanks !

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZExD4IwEIX/iukMSXuVimOrDJLUGKKDIQwYwJAYa4gL/17qYB7gSNKh311e37trnjNbtg/BAiZ4yGVInDwc9qwIcjbchnPUNkG8WJNkUDhfT9jnXABlrurau4OKILlGdf+qV3qkJ6DU1UAyjuRUu/sW/BjkXxeLjYFOad00gNtoM8uhfzmkl6uQ0zI5cFvG3YCI5usYrzIC0s+qB4xjRVOxGYkVGpfdGz9CkvjjXHwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t, Date = _t, Body.Text = _t]),
    #"Grouped by Subject" = Table.Group(Source, {"Subject","Date"}, {"ar", each _}, 0, (x,y) => Number.From(y[Subject]<>"")),
    Extracted = Table.TransformColumns(#"Grouped by Subject", {"ar", each let all=[Body.Text], hdr=List.FirstN(all,4), body=List.Split(List.Skip(all,4),4) in #table(hdr,body)}),
    #"Expanded ar" = Table.ExpandTableColumn(Extracted, "ar", {"ID", "NAME", "NUMBER", "TYPE"}, {"ID", "NAME", "NUMBER", "TYPE"})
in
    #"Expanded ar"