Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a power automate which get email contents but the contents are all in single row or cell. Does anyone knows how to my desire output? I have attached the pbi file in this link
https://www.dropbox.com/s/dxwuy038cjana2p/Transformation.pbix?dl=0
Thanks in advance
Solved! Go to Solution.
You can split by line feed, remove excess spaces, split by space, promote headers, and cleanup.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZNNb8IwDIb/StQzUpPY+arEgUE5IG3anfTQQaVNYtlUtmn8+7WNg8o+LoMjyeFt7Npx9NjrdcZFLnQuuZRMQgGyQJFNMv/OOWy2Tx9ss6v3+6nPys+3pg31bt6fweh5CctZeaMX6KyxjnNeKi0WaDVK7mOCZp2iKh/6Pf3v8uGufm5Yv1Z1GHTZPAzKbut20NlrmwyHs666b7qi+0RWxYTKWPqQUXXyoPZh9fIYK0ITjc6Q1+qooI4eH7pyD3RgJ2Fd1qhGUzyYs97R7QFj3nGMPHzIqskP5rZQ8sqcmCd2CjjhkakJIHlGzB0xc1qeMjcIyTBiDvgHc6vIY4BfGPovyBELZa/Iv405kjIUBIWY9DDHc06IZeoVRwahXOqZ8ZzrhJp+16kJ4Aj/4sirLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmailDate = _t, EmailBody = _t]),
#"Split Column by New Line" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"EmailBody", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EmailBody"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by New Line", each [EmailBody] <> "" and not Text.Contains([EmailBody], "=") and not Text.Contains([EmailBody], "<")),
#"Transformed Column" = Table.TransformColumns(#"Filtered Rows",{{"EmailBody", each Text.Combine(List.Select(Text.Split(_, " "), each _ <> ""), " "), type text}}),
#"Split Column by Space" = Table.SplitColumn(#"Transformed Column", "EmailBody", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Space", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Jan] <> "Jan")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"01/16/2022 23:32:41", "EmailDate"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"EmailDate", type datetime}, {"Name", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}})
in
#"Changed Type"
You can split by line feed, remove excess spaces, split by space, promote headers, and cleanup.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZNNb8IwDIb/StQzUpPY+arEgUE5IG3anfTQQaVNYtlUtmn8+7WNg8o+LoMjyeFt7Npx9NjrdcZFLnQuuZRMQgGyQJFNMv/OOWy2Tx9ss6v3+6nPys+3pg31bt6fweh5CctZeaMX6KyxjnNeKi0WaDVK7mOCZp2iKh/6Pf3v8uGufm5Yv1Z1GHTZPAzKbut20NlrmwyHs666b7qi+0RWxYTKWPqQUXXyoPZh9fIYK0ITjc6Q1+qooI4eH7pyD3RgJ2Fd1qhGUzyYs97R7QFj3nGMPHzIqskP5rZQ8sqcmCd2CjjhkakJIHlGzB0xc1qeMjcIyTBiDvgHc6vIY4BfGPovyBELZa/Iv405kjIUBIWY9DDHc06IZeoVRwahXOqZ8ZzrhJp+16kJ4Aj/4sirLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmailDate = _t, EmailBody = _t]),
#"Split Column by New Line" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"EmailBody", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "EmailBody"),
#"Filtered Rows" = Table.SelectRows(#"Split Column by New Line", each [EmailBody] <> "" and not Text.Contains([EmailBody], "=") and not Text.Contains([EmailBody], "<")),
#"Transformed Column" = Table.TransformColumns(#"Filtered Rows",{{"EmailBody", each Text.Combine(List.Select(Text.Split(_, " "), each _ <> ""), " "), type text}}),
#"Split Column by Space" = Table.SplitColumn(#"Transformed Column", "EmailBody", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv)),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Space", [PromoteAllScalars=true]),
#"Filtered Rows1" = Table.SelectRows(#"Promoted Headers", each ([Jan] <> "Jan")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"01/16/2022 23:32:41", "EmailDate"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"EmailDate", type datetime}, {"Name", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}})
in
#"Changed Type"
I should say Your data is hard for ME to see but I'm not wearing my glasses.
It's hard to see your data, but it's a familiar setup. To get yourself going, split by delimiter using Special Characters, and use #(CR) and #(LF) (carriage return and line feeds.
--Nate