Transform multiline records into a table


This post was moved here.

Comments (7)

  1. Rajender says:

    Very well explained in details. Thanks a lot for sharing this 🙂

  2. I did something very similar in analyzing XML version of SQL Server Profiler trace logs:

    let
    Source = Xml.Tables(File.Contents("C:UsersdonpDownloadstrace.xml")),
    Table1 = Source{1}[Table],
    Table = Table1{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table,{{"Attribute:id", Int64.Type}, {"Attribute:name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Attribute:name"] = "Query End")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Attribute:id", "Attribute:name"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Column"}),
    #"Expand Column" = Table.ExpandTableColumn(#"Reordered Columns", "Column", {"Attribute:name", "Element:Text"}, {"Attribute:name", "Element:Text"}),
    #"Pivoted Column" = Table.Pivot(#"Expand Column", List.Distinct(#"Expand Column"[#"Attribute:name"]), "Attribute:name", "Element:Text"),
    in
    #"Pivoted Column"

  3. Anonymous says:

    Excel for Security Analysts is a series of blog posts for Security, Data and BI analysts. In this series

  4. Anonymous says:

    Excel for Security Analysts

  5. ah some sort of super transpose?

  6. Gil Raviv says:

    Super transpose – I like this name 🙂

Skip to main content