Transpose every 5 rows into Column

Hi howeixiong,

Give this a go:

let
    Source = YourTable,
    GroupedRows = Table.Group(Source, {"FieldDescription"}, {{"temp", each _[Value] }}),
    NewTable = Table.FromColumns( GroupedRows[temp], GroupedRows[FieldDescription] )
in
    NewTable

I hope this is helpful

Hi howeixiong 

Another solution

let
Source = Your_Source,
IntergerDivide = Table.TransformColumns(Source, {{"Index", each Number.IntegerDivide(_, 5), Int64.Type}}),
Pivot = Table.Pivot(IntergerDivide, List.Distinct(IntergerDivide[FieldDescription]), "FieldDescription", "Value"),
Remove_Index = Table.RemoveColumns(Pivot,{"Index"})
in
Remove_Index

Stéphane 

You can solve this problem in different way one is splitting the table every 5 rows as the below code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc+3DcAwDETRVQzWKpRDKYcN3BHafw3TZxCgCxX3ig+KmTw5mvLOec8t0HJMQeauFEFR5qGUQOmdShmUZV5KBVRsvoKqzTdQs/kO6jY/QMPmg/9u/d8vH1gP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, #"field description" = _t, value = _t]),
    #"Removed Columns" = Table.Combine(List.Transform(Table.Split(Table.RemoveColumns(Source,{"index"}),5),each Table.PromoteHeaders(Table.Transpose(_))))
in
    #"Removed Columns"

Hi howeixiong, another solution:

similar to AlienSx, but handles null values if needed

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc+3DcAwDETRVQzWKpRDKYcN3BHafw3TZxCgCxX3ig+KmTw5mvLOec8t0HJMQeauFEFR5qGUQOmdShmUZV5KBVRsvoKqzTdQs/kO6jY/QMPmg/9u/d8vH1gP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, FieldDescription = _t, Value = _t]),
    Transformed = [ a = List.Buffer(Source[Value]),
    b = List.Split({0..Table.RowCount(Source)-1}, 5),
    c = List.TransformMany(b, each {List.Transform(_, (x)=> a{x})}, (x,y)=> if List.Count(y) = 5 then y else y & List.Repeat({null}, 5 - List.Count(y)) ),
    d = #table(List.FirstN(Source[FieldDescription], 5), c )
  ][d]
in
    Transformed