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

     

jimmyfromus's avatar
jimmyfromus
Helper III
5 years ago

CNENFRNL Thanks for following up. Here's the step before renaming the columns. 

 

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

jimmyfromus , all seems normal to me. Subsequent steps are supposed to work well.

  • jimmyfromus's avatar
    jimmyfromus
    Helper III
    5 years ago

    CNENFRNL I just had to change the last line slightly. Thanks again. 

     

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

in
    #"Rearrange"
  • dannytan111289's avatar
    dannytan111289
    Frequent Visitor
    4 years ago

    Hi jimmyfromus mind to share your pbix? i have the same problem over here