Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 

Thanks in advance

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks Anonymous 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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"