Help in Pivot single column with Index /2

Hi Anonymous ,

 

Try this. It still needs filtering to keep only complete rows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKcpXitWJVjLQM7QwNDIFswOKUnWDCoth4gYmhmZAYK4UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Course = _t]),
    addIndex1 = Table.AddIndexColumn(Source, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeSelf = Table.NestedJoin(addIndex0, {"Index1"}, addIndex0, {"Index0"}, "addIndex0", JoinKind.LeftOuter),
    expandMergeSelf = Table.ExpandTableColumn(mergeSelf, "addIndex0", {"Course"}, {"Course.1"})
in
    expandMergeSelf

 

SUMMARY:

1) Create index starting from 1

2) Create index starting from 0

3) Merge table to self on [Index1] = [Index0]

4) Expand [Course] column

 

Pete

If you transform your [Index] column modulo 2 to get alternating 0 and 1, then you can pivot that column with [Course] as the values:

 

Full query with some bonus material:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKcpPKU0uyczPU4rViVYy0DO0MDQyBbMDilJ1g1ILSzOLM0tSi2HSBiaGZkBgDuYH56eVlCcWpSr4paampKZA1RiZmVqaGxkZgbnhmXkKnnnFJYk5OYnI1piYWhiDAJjvm5hMQFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Course = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Extract", each Number.IntegerDivide([Index],2), Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Custom", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US")[Index]), "Index", "Course"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "Course"}, {"1", "Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Duration", each Duration.From([Time]), type duration)
in
    #"Added Custom1"

 

 

Streamlined version with just the key parts:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swrKcpPKU0uyczPU4rViVYy0DO0MDQyBbMDilJ1g1ILSzOLM0tSi2HSBiaGZkBgDuYH56eVlCcWpSr4paampKZA1RiZmVqaGxkZgbnhmXkKnnnFJYk5OYnI1piYWhiDAJjvm5hMQFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Course = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Extract", each Number.IntegerDivide([Index], 2), Int64.Type),
    TransformIndex = Table.TransformColumns(#"Added Custom",
        {{"Index", each if Number.Mod(_, 2) = 0 then "Course" else "Time", type text}}),
    #"Pivoted Column" = Table.Pivot(TransformIndex , List.Distinct(TransformIndex[Index]), "Index", "Course")
in
    #"Pivoted Column"