cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodfernandez
Helper I
Helper I

Split Column into multiple Columns and Rows

 

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

SubjectDateBody.Text
Mail110-03-2021ID
NAME
NUMBER
TYPE
001
Rodrigo
1234
Type A
002
Joe
3853
Type C
Mail211-03-2021ID
NAME
NUMBER
TYPE
003
Jeff
9573
Type A
Mail306-02-2021ID
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

SubjectDateIDNAMENUMBERTYPE
Mail110-03-2021001Rodrigo1234Type A
Mail110-03-2021002Joe3853Type C
Mail211-03-2021003Jeff9573Type A
Mail306-02-2021004Bob2253Type A
Mail306-02-2021005Andy8862Type B
Mail306-02-2021006Bart3321Type 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 !



1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-05-24 205751.png

View solution in original post

1 REPLY 1
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-05-24 205751.png

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors