Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

PQ question - change rows to columns

Hi, Can anyone help me out with some applied steps for the below? I've tried a mixture of transpose, promote first row to header, unpivot, etc; however, so far am not having any luck! In the sa...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    1 year ago

    After loading your table, you can insert this step where Source is your previous step

    = Table.FromRecords(List.Transform(Table.Split(Source, 3), (x)=> Record.FromList(x[Column2], x[Column1])))

     

    The sample code in action here

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HwS8xNVdJRclSK1YlWcs4vLSpOVQjJLMkBC0JEXRJLUhU8i4tLU1OAgoYG+oZG+kYGRiZgSTRznLCZ4+SExRwTfUNDnMY4YzPG2RmHc8DmmGIzxwWbOS4u+MwxVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Result = Table.FromRecords(List.Transform(Table.Split(Source, 3), (x)=> Record.FromList(x[Column2], x[Column1])))
in
    Result

     

    If you are looking for a complete point and click solution, below is one which use Pivot feature

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HwS8xNVdJRclSK1YlWcs4vLSpOVQjJLMkBC0JEXRJLUhU8i4tLU1OAgoYG+oZG+kYGRiZgSTRznLCZ4+SExRwTfUNDnMY4YzPG2RmHc8DmmGIzxwWbOS4u+MwxVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 3, type number}}),
    #"Rounded Up" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundUp, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

     

  • slorin's avatar
    slorin
    1 year ago

    Hi Anonymous 

     

    = #table(List.FirstN(Source[Column1],3), List.Split(Source[Column2],3))

    Stéphane 

collinsg's avatar
collinsg
Icon for Solution Sage rankSolution Sage
1 year ago

Good day scott_86_

You already have solutions. I'll try to explain what is happening intuitively in the pivot.

 

In laying out a pivot table you consider,

  1. What is to go in rows?
  2. What is to go in columns?
  3. What is to go in values?

In your case these would be

  1. Rows: students
  2. Columns: attributes of the students ("Course Title" and "Date Issued")
  3. Values: the values of the attributes

Intuitively the problem is that three things need specified but you are starting with only two columns. "Column 1" entangles what you want in rows (students as represented by "Student Name") and the attributes of the students.

 

The pivot solution is to disentangle "Column 1".

  1. Add a column just for "Student Name" (steps "Added Custom" and "Filled Down" in code sample).
  2. Disentangle "Student Name" from "Column 1" by filtering to remove rows containing "Student Name" in "Column 1".

    The Table.Pivot function [Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table] can now be fed with the parameters it wants from your column names, pivotValues is the Student Name column (or to be precise, the distinct values in that column),  attributeColumn is "Column 1" and valueColumn is "Column 2".
  3. Pivot

     

Here is sample code. It will be flexible as regards the number of students as there is no hard-wiring of the number of students.

Here is sample code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HwS8xNVdJRCk7OLylRitWJVnLOLy0qTlUIySzJAUkEuDmChV0SS1IVPIuLS1NTgKIGhvpAZGRgZKhgYGAFRmBVaKa6JJZlppBhqhFeU30zs1PJMNQYydBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Student Name", each if [Column 1] = "Student Name" then [Column 2] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Student Name"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column 1] <> "Student Name")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Column 1]), "Column 1", "Column 2"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Student Name", type text}, {"Course Title", type text}, {"Date Issued", type datetime}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Date Issued", type date}})
in
#"Changed Type2"

Hope this helps