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

     

StefanoGrimaldi's avatar
StefanoGrimaldi
Resident Rockstar
5 years ago

you have a problem there, cause you dont have a way to identify that id 40 its the first line for example and that the uni 1 value of 14 goes with id 40 for example. 

if you had ID 1 = 40, number 1 = 3434 you would have a way to do so, but not the caso so you dont knoe what value goes to each row.