Forum Discussion
Arranging data
Good morning guys, I have a table which shown in below and need to convert it in well manner. Anyone have idea how may i do it. Thank you guys Cheers Oli
Hi Anonymous ,
provided a stable structure like in your sample, you can do it like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1KTbFS0lEyNNA3MNY3MjAyBHJAKFYnWsk5JzM1rwSsyi8fpErZyNTE3MwCyAopSswrhgiamhsYWJqDNZBpoCUWAy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}), #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Table.FromRows( {List.Select( List.Union( Table.ToColumns( Table.SelectColumns(_,{"Column2", "Column4"}) ) ), each _ <> null)})}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}) in #"Expanded All"Next time, please provide sample data in a usable form, so that we don't have to type that in manually:
ImkeF
Community Champion
Community ChampionHi Anonymous ,
provided a stable structure like in your sample, you can do it like so:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1KTbFS0lEyNNA3MNY3MjAyBHJAKFYnWsk5JzM1rwSsyi8fpErZyNTE3MwCyAopSswrhgiamhsYWJqDNZBpoCUWAy2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"All", each Table.FromRows( {List.Select( List.Union( Table.ToColumns( Table.SelectColumns(_,{"Column2", "Column4"}) ) ), each _ <> null)})}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"})
in
#"Expanded All"Next time, please provide sample data in a usable form, so that we don't have to type that in manually: