Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help in data transformation

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 

marc_hll_0-1642462586437.png

Thanks in advance

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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"

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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"
Anonymous
Not applicable

Thanks @AlexisOlson 

watkinnc
Super User
Super User

I should say Your data is hard for ME to see but I'm not wearing my glasses. 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

Thanks @watkinnc 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors