Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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 !
Solved! Go to 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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
107 | |
68 | |
48 | |
48 | |
44 |