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 

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
1 year ago

Anonymous 

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