PQ question - change rows to columns

[scott_86_]

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 sample data photo below, I am trying to achieve the following:

[Vijay_A_Verma · Accepted solution]

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"

[Anonymous]

Hi @scott_86_ ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some virtual sample data, and the expected results.

Best Regards,

Stephen Tao

[Omid_Motamedise]

Interesting question.
Consider the next table as Source

the result of next formula would be alist includineg every three rows of Sourec table.
= Table.Split(Source,3)

while we neeed to transpose evert tables, so instead of the previous formula use the next formula which result in the next image.

= List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_)))

based on the above explanation, use the next formula instead of the previos formula which solve the problem as below.

= Table.Combine(List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_))))

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[AntrikshSharma]

@scott_86_ 

let
    Source = RawData,
    GroupedRows = 
        Table.Group (
            Source,
            { "Column1" },
            {
                {
                    "Transformation",
                    each Table.TransformColumnTypes (
                        Table.PromoteHeaders (
                            Table.Transpose (
                                _,
                                type table [
                                    Student Name = text,
                                    Course Title = text,
                                    Date Issued = date
                                ]
                            )
                        ),
                        { { "Date Issued", type date } },
                        "en-GB"
                    ),
                    type table [ Student Name = text, Course Title = text, Date Issued = text ]
                }
            },
            GroupKind.Local,
            ( x, y ) => Number.From ( y[Column1] = "Student Name" )
        ),
    RemovedColumns = 
        Table.RemoveColumns ( GroupedRows, { "Column1" } ),
    ColumnNames = 
        Table.ColumnNames ( RemovedColumns[Transformation]{0} ),
    ExpandedCount = 
        Table.ExpandTableColumn (
            RemovedColumns,
            "Transformation",
            ColumnNames,
            ColumnNames
        )
in
    ExpandedCount

[collinsg]

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

[slorin · Accepted solution]

Hi @scott_86_ 

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

Stéphane 

[Vijay_A_Verma · Accepted solution]

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"