Split dataset into columns and rows

[alevandenes]

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

[m_dekorte · Accepted solution]

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

[alevandenes]

THANK YOU