cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jimmyfromus
Helper III
Helper III

Make certain rows into headers

Hi, 

 

I've the following table: 

data    Numbers

id40
number3434
year2007
rank23
school1
uni 114
uni 210
id343
number78778
year2007
rank87
school54
uni 12
uni 230

 

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: 

56t.jpg

 

Thanks for any help. 

1 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

 

Screenshot 2021-01-09 224628.png

Screenshot 2021-01-09 224810.png

View solution in original post

17 REPLIES 17
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





CNENFRNL
Community Champion
Community Champion

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.Screenshot 2021-01-09 203155.png

@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

 

Screenshot 2021-01-09 224628.png

Screenshot 2021-01-09 224810.png

View solution in original post

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

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

 

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

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

 

57t.jpg

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

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

@CNENFRNL Could I PM you the pbix file?

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

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? 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




@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

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Super User
Super User

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors