Forum Discussion

jimmyfromus's avatar
jimmyfromus
Helper III
5 years ago
Solved

Make certain rows into headers

Hi,    I've the following table:  data    Numbers id 40 number 3434 year 2007 rank 23 school 1 uni 1 14 uni 2 10 id 343 number 78778 year 2007 rank 87 ...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    jimmyfromus , StefanoGrimaldi , as long as each group of records starts with ID like this, this solution applies; order and presence of other fields (number, year, rank ...) make no difference.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykxR0lEyNDRUitWJVsorzU1KLQIKGJsYm4BFKlMTQXwjAwNzML8oMS8bxDcG84qTM/Lzc0AGgLmleZkKhiCeCZxrBOIagLlgm4yMjPCZa2GOaq4pqknGBqjONLcwN7dAsdoIYZWxsTFNvGBigj1oSHJkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),

    Rearrange = Table.Combine(List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
    Rearrange

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc07CoAwEEXRrcjUFvmMTPYSUvgDgxpBSeHuzYwgpLE8r3jXe4gTtIAKQush5X2Yz2KLFmW5555tlCLx2aeVbUXXuBzHVqyFOcVGs/CjYb7nEirHdYkckftLOapTHVYtU6VsSYUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data = _t, Numbers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}, {"Numbers", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"data"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Numbers", "Index", "Index1"}, {"Numbers", "Index", "Index1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[data]), "data", "Numbers"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"id", Int64.Type}, {"number", Int64.Type}, {"year", Int64.Type}, {"rank", Int64.Type}, {"school", Int64.Type}, {"uni 1", Int64.Type}, {"uni 2", Int64.Type}})
in
    #"Changed Type1"

Hope this helps.