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

     

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, jimmyfromus , you might want to try the following codes,

 

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]),
    #"All Columns" = Table.ToColumns(Source),
    #"New Table" = Table.PromoteHeaders(Table.Transpose(Table.FromColumns({List.Skip(#"All Columns"{0},7)} & List.Split(#"All Columns"{1}, 7))), [PromoteAllScalars=true])
in
    #"New Table"

or

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

    Custom1 = Table.Combine(List.Transform(Table.Split(Source,7), each Table.PromoteHeaders(Table.Transpose(_),[PromoteAllScalars=true])))
in
    Custom1

 

 

here's another solution, which is only a matter of several clicks on UI,

 

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]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Integer-Divided Column", {{"Index", type text}}, "en-US")[Index]), "Index", "Numbers"),
    #"Transposed Table" = Table.Transpose(#"Pivoted Column"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

 

All solutions achieve the same result.

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    but that would work only if the list its in cronological order no? if the second Id was the one that dhould be as second row it wont, unless you assume its allways in cronological order as it was inputed the data? 

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

      StefanoGrimaldi , better to learn more and to comment less

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

    Custom1 = Table.Combine(List.Transform(Table.Split(Source,7), each Table.PromoteHeaders(Table.Transpose(_),[PromoteAllScalars=true])))
in
    Custom1
      • StefanoGrimaldi's avatar
        StefanoGrimaldi
        Resident Rockstar
        5 years ago

        I asked because I see your solution does work, but work under one scenario, I asked will it work in the scenario I put in? cause if the case if the data isnt stored in cronological order ifor each item on the file for example that code wont know how to assign to each row each value, or will it and how? would be learning more if you can answer that question 🙂 as you can see the guy that posted the topic its saying about that same topic of the cronological order of the data and isnt right. 

  • jimmyfromus's avatar
    jimmyfromus
    Helper III
    5 years ago

    CNENFRNL , StefanoGrimaldi Thanks, As Stefano mentioned the data, as I've just noticed, is not all in chronolgical order. ID for example is in row 1,8,15 but then 21. What can I do about that? Thanks. 

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      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

       

      • jimmyfromus's avatar
        jimmyfromus
        Helper III
        5 years ago

        CNENFRNL That's great, thanks. I'm just a little stuck on the final part. I renamed the column to data in the step before but I get the error: Expression.Error: The column 'data' of the table wasn't found.Details: data. 

            #"Transposed Table" = Table.Transpose(Source),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Transposed Table", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ":"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Column1], ":"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "data"}}),
    #"Rearrange" = Table.Combine(#"Renamed Columns",List.Transform(Table.Group(Source, "data", {{"grouped", each _}}, GroupKind.Local, (s,c)=>Number.From(c="id"))[grouped], each Table.PromoteHeaders(Table.Transpose(_))))
in
    #"Rearrange"