Forum Discussion
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 |
| 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.
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
17 Replies
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 Custom1here'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.
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?
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
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.
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
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.
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.
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.
