How to treat table with stacked data

Hello AndreWahlbuhl 

 

I don't know how the document header is structured and if you need this information somehow too. I assume that you have anonymous column header and then stacked information whereas one row is structured in 3 rows like this. In case you have a document header, you would need somehow to eliminate your header (by deleteing the first rows or by checking one of your column until a certain value is reached - maybe "Name" in column1

 

 

In this case you can split the table with pagesize 3 and then transform the list of tables by referencing the column and the row. After that the records can be transformed to a table. Would you need also something of your document header, you could now add an additional column where you referencing your first step.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+7CsMwDEV/RXgOwXGW0rnN2KFJJ5PBUEEMrh0kJdC/b+I+SDsVNIhzH0jWqpO7oSrUZ1pxMnFecGTVF3ZFJHhdWOOj5yGv5ylC53P2gGNId7gw0qtmTR2JEm2b38Lq09UWN86HXJp1U5eVKY02GvRub8zC/0MdsrRI89cZz26QBIScwowwJBaIP29ne/8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    SplitTable = Table.Split(Source,3),
    TransformListsOfTables = List.Transform
    (
        SplitTable,
        each [1= [Column1]{0}, 2= [Column5]{0}, 3= [Column6]{0}, 4= [Column1]{1}, 5= [Column2]{1}, 6= [Column3]{1}, 7= [Column4]{1}, 8= [Column1]{1}]
    ), 
    CreateTableFromRecords= Table.PromoteHeaders(Table.FromRecords(TransformListsOfTables))
in
    CreateTableFromRecords

 

this is the outcome

Copy paste this code to the advanced editor in a new blank query to see how the solution works. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy