Forum Discussion

alevandenes's avatar
alevandenes
Helper IV
2 years ago
Solved

Split dataset into columns and rows

Hi,

i would like some help. i need to analyze in powerBI some machine data, that unfortunately are provided to me this way.

I would need to have the rows from SO# to finished time into columns and the rest as different rows. Do you know of a way to do this?

 

This is the structure i would like to have:

 

Thanks a lot for your help,

Alessandra

Need Help
  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Hi alevandenes,

     

    Created a simple mock up table, that follows a similair structure.

    You can copy this into a new blank query, replacing everything that is there, with this code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc87DoNADIThq0RbUzCzT/qUuQGiWCQ6Su4vVkhGLkbp7N/N53UNv74f5wdhCt9+9TFsk0VapIvRYnQxWUwuZovZxWKxuFgtVhebxfbEsbnj612UF7MCA0oMKjKiMiMpNLJSo/xjo0p3k+5FuTkrN6HcpHIzKjfHj9sN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    NoBlanks = Table.SelectRows(Source, each [Column 1] <> null and [Column 1] <> ""),
    SplitTable = Table.Split( NoBlanks, 8 ),
    Transform = List.Transform( SplitTable, each Table.PromoteHeaders( Table.Transpose(_))),
    Combine = Table.Combine( Transform )
in
    Combine

    As you can see, I've broken it down into a base pattern, or several steps. Removing blank labels, Splitting the table, Transforming the nested tables and combining them. Here's the result.

     

    I hope this is helpful

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    2 years ago

    Hi alevandenes,

     

    Created a simple mock up table, that follows a similair structure.

    You can copy this into a new blank query, replacing everything that is there, with this code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc87DoNADIThq0RbUzCzT/qUuQGiWCQ6Su4vVkhGLkbp7N/N53UNv74f5wdhCt9+9TFsk0VapIvRYnQxWUwuZovZxWKxuFgtVhebxfbEsbnj612UF7MCA0oMKjKiMiMpNLJSo/xjo0p3k+5FuTkrN6HcpHIzKjfHj9sN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
    NoBlanks = Table.SelectRows(Source, each [Column 1] <> null and [Column 1] <> ""),
    SplitTable = Table.Split( NoBlanks, 8 ),
    Transform = List.Transform( SplitTable, each Table.PromoteHeaders( Table.Transpose(_))),
    Combine = Table.Combine( Transform )
in
    Combine

    As you can see, I've broken it down into a base pattern, or several steps. Removing blank labels, Splitting the table, Transforming the nested tables and combining them. Here's the result.

     

    I hope this is helpful