Forum Discussion
arifulice09
3 years agoHelper I
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.
- 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 - 3 years ago
AntrikshSharma wow what a solution is it ! Thanks a lot .
arifulice09
3 years agoHelper I
AntrikshSharma wow what a solution is it ! Thanks a lot .