Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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 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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
13 |