cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mythbusternz
Helper I
Helper I

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
Helper I
Helper I

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"

 

View solution in original post

@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

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors