Make certain rows into headers

[jimmyfromus]

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
school	54
uni 1	2
uni 2	30

I would like the following result but am unsure how to get there. I tried a transpose but this moves all rows as columns. My desired result is as follows: 

Thanks for any help. 

[Ashish_Mathur]

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.

[parry2k]

@jimmyfromus if the source is csv or text file I will use the text by example and it will work just fine. read more here.

[CNENFRNL]

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]

@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 · Accepted solution]

@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

[dannytan111289]

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

[jimmyfromus]

@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]

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

[jimmyfromus]

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

[CNENFRNL]

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

[dannytan111289]

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

[jimmyfromus]

@CNENFRNL Could I PM you the pbix file?

[jimmyfromus]

@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"

[StefanoGrimaldi]

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]

@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]

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. 

[StefanoGrimaldi]

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.