Help in Pivot single column with Index /2

[mythbusternz]

Hi Experts, 

I have data and time in alternative rows, I need to convert to 2 columns Data and Time.

Please can you help me to pivot it, I could able to reach to this point and but however way I tried to pivot or unpivot and use combinations, I am not able to proceed.

Output Needed 

Course

Introduction	4:21
Pre-Requisites	2:30
Software Needed	6:23
Win Installation	3:30
Mac Installation	3:30

M code till now:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Course ", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Extract", each Number.IntegerDivide([Index],2))
in
    #"Added Custom"

Help is appreciated along with some help to Perfect M code

[BA_Pete · Accepted solution]

Hi @mythbusternz ,

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

[mythbusternz]

Thank you @AlexisOlson  and @BA_Pete  I tried both your solutions both worked and thank you very much. 

[AlexisOlson · Accepted solution]

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"

[mythbusternz]

@AlexisOlson Also Thank you as I practice, for your explanation. Awesome

[BA_Pete · Accepted solution]

Hi @mythbusternz ,

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