Transpose every 5 rows into Column

[howeixiong]

Hi there,

I am writing in to seek guidance.

I would like to perform the following.

Before:

Result:

Thank you

[m_dekorte · Accepted solution]

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

[slorin · Accepted solution]

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 

[Omid_Motamedise · Accepted solution]

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"

[Anonymous]

Can't you just remove the index, then use the Pivot GUI, using FieldDescription as the pivot column and Values as the values, choosing "Do Not Aggregate"? Seems like the solutions above depend on there being exactly 5 unique values in FieldDescription.

--Nate

[dufoq3 · Accepted solution]

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

[AlienSx]

#table(List.FirstN(Source[FieldDescription], 5), List.Split(Source[Value], 5))

[Omid_Motamedise · Accepted solution]

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"

[slorin · Accepted solution]

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 

[m_dekorte · Accepted solution]

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