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.

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"

       

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        jimmyfromus , what does your table look like until the step #"Renamed Columns"?

    • dannytan111289's avatar
      dannytan111289
      Frequent Visitor
      4 years ago

      Hi,

       

      I have a question, I copy-paste the

      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

      and I changed "data" into "F1" (my first column name). However, I received Token Comma Expected for Rearrange. Any ideas? Thanks