Forum Discussion

arifulice09's avatar
arifulice09
Helper I
3 years ago
Solved

Power Query row transformation into column

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's avatar
    3 years ago

    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