Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Datanaut!
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"
Thank you @AlexisOlson and @BA_Pete I tried both your solutions both worked and thank you very much.
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"
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.