Power Query row transformation into column

[arifulice09]

I have data in rows I need to convert into column (please find the attached), I have triyed several times but not getting the expected result.

[AntrikshSharma · Accepted solution]

@arifulice09 Use this:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45W8g8NCfZ0cVUI8XBVCA7xD3JV0lFy8/EPr3HUdQty9HVVcDRUitUhRp0RkeqMweqc3UL8gVIgyhBdwAhdAKIl3D/Ix0XBOTQAKGpoYKgQnl+Uk6LgXFqARdYIr6wxmqyrY4hCuKuPD1ASxlQwMjDCI2eMR85EKTYWAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Heading = _t, Description = _t ]
        ),
    GroupedRows = 
        Table.Group (
            Source,
            { "Heading" },
            { { "Rows", each _, type table [ Heading = nullable text, Description = nullable text ] } }
        ),
    AddedIndex = Table.AddIndexColumn ( GroupedRows, "Index", 1, 1, Int64.Type ),
    AddedCustom = 
        Table.AddColumn (
            AddedIndex,
            "Custom",
            each
                let
                    Index = [Index],
                    ColumnNames = 
                        List.Transform (
                            Table.ColumnNames ( [Rows] ),
                            each _ & " " & Text.From ( Index )
                        ),
                    Data = 
                        {
                            { [Rows][Heading]{0} }
                                & List.Repeat ( { null }, List.Count ( [Rows][Description] ) - 1 ),
                            [Rows][Description]
                        },
                    Result = 
                        Table.ToColumns (
                            Table.FromRows ( { ColumnNames } ) 
                                & Table.FromColumns ( Data )
                        )
                in
                    Result
        ),
    CombineListsIntoTable = 
        Table.PromoteHeaders (
            Table.FromColumns ( List.Combine ( AddedCustom[Custom] ) )
        )
in
    CombineListsIntoTable

[arifulice09 · Accepted solution]

@AntrikshSharma   wow what a solution is it ! Thanks a lot .

[AntrikshSharma · Accepted solution]

@arifulice09 Use this:

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45W8g8NCfZ0cVUI8XBVCA7xD3JV0lFy8/EPr3HUdQty9HVVcDRUitUhRp0RkeqMweqc3UL8gVIgyhBdwAhdAKIl3D/Ix0XBOTQAKGpoYKgQnl+Uk6LgXFqARdYIr6wxmqyrY4hCuKuPD1ASxlQwMjDCI2eMR85EKTYWAA==",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Heading = _t, Description = _t ]
        ),
    GroupedRows = 
        Table.Group (
            Source,
            { "Heading" },
            { { "Rows", each _, type table [ Heading = nullable text, Description = nullable text ] } }
        ),
    AddedIndex = Table.AddIndexColumn ( GroupedRows, "Index", 1, 1, Int64.Type ),
    AddedCustom = 
        Table.AddColumn (
            AddedIndex,
            "Custom",
            each
                let
                    Index = [Index],
                    ColumnNames = 
                        List.Transform (
                            Table.ColumnNames ( [Rows] ),
                            each _ & " " & Text.From ( Index )
                        ),
                    Data = 
                        {
                            { [Rows][Heading]{0} }
                                & List.Repeat ( { null }, List.Count ( [Rows][Description] ) - 1 ),
                            [Rows][Description]
                        },
                    Result = 
                        Table.ToColumns (
                            Table.FromRows ( { ColumnNames } ) 
                                & Table.FromColumns ( Data )
                        )
                in
                    Result
        ),
    CombineListsIntoTable = 
        Table.PromoteHeaders (
            Table.FromColumns ( List.Combine ( AddedCustom[Custom] ) )
        )
in
    CombineListsIntoTable