cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mythbusternz
Advocate II
Advocate II

Help in Pivot single column with Index /2

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.

Pivot_help.PNG

Output Needed 

Course

Introduction4:21
Pre-Requisites2:30
Software Needed6:23
Win Installation3:30
Mac Installation3: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

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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

View solution in original post

AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1638390047552.png

 

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"

 

View solution in original post

4 REPLIES 4
mythbusternz
Advocate II
Advocate II

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

AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1638390047552.png

 

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"

 

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

BA_Pete
Super User
Super User

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!