Forum Discussion
Moving Rows into columns with Unique ID
Hello, I have a much larger set of data similar to below. I want to get it so Unique ID is in the row, and the Course Title is in the column.
So from this:
| Title | Learner ID | Learner Name | Status | Division |
| Course 1 | ID1 | Bob | Completed | Payroll |
| Course 1 | ID2 | Sarah | Completed | HR |
| Course 1 | ID3 | Alice | Completed | Sales |
| Course 1 | ID4 | Mark | Incomplete | Marketing |
| Course 2 | ID1 | Bob | Incomplete | Payroll |
| Course 2 | ID2 | Sarah | Incomplete | HR |
| Course 2 | ID3 | Alice | Incomplete | Sales |
| Course 2 | ID4 | Mark | Completed | Marketing |
| Course 3 | ID1 | Bob | Completed | Payroll |
| Course 3 | ID2 | Sarah | Completed | HR |
| Course 3 | ID3 | Alice | Incomplete | Sales |
| Course 3 | ID4 | Mark | Completed | Marketing |
To this:
| Learner ID | Learner Name | Course 1 | Course 2 | Course 3 | Division |
| ID1 | Bob | Completed | Incomplete | Completed | Payroll |
| ID2 | Sarah | Completed | Incomplete | Completed | HR |
| ID3 | Alice | Completed | Incomplete | Incomplete | Sales |
| ID4 | Mark | Incomplete | Completed | Completed | Marketing |
I've tried Pivot Columns but it doesnt merge the ID.
Thanks in advance.
Hi Anonymous ,
Thanks for amitchandak reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:Click "Transform data" and open power query
Select the Title column and select the pivot column in the Transform toolbar, then in the screen that opens select Status in the value column and select the Don't Aggregate option in the Aggregate Value function.Select the Title column and select the pivot column in the Transform toolbar, then in the screen that opens select Status in the value column and select the Don't Aggregate option in the Aggregate Value function.Final output
You can also use this m code in advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZCxDsIgEIZfpWHuIvgC2g420cZYt6bDWS9KpGCAmvj2QtKhQAe7/Lk7/v/ycW1LrtwKJDk5ImiJOqvKWVPD4N8aC3Y0rij5hxuuJOnylhRq1AazjZtXpde9ujkt1PAWaPHu6jN8tRIitVO/FTQ8o8DhknqZ053gPUbeBgSa1L51egL98o3sJ/80Q8vlYx6hEXyQWKCnCX2QCPFpgh+YE34a889/u4jP1t2erbg9WwnP/oTvfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Learner ID", type text}, {"Learner Name", type text}, {"Status", type text}, {"Division", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Title]), "Title", "Status") in #"Pivoted Column"Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Anonymous , Unpivot should help in this case
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Learn Power BI: Power Query Table.Unpivot, Table.UnpivotOtherColumns: https://youtu.be/0FEGEAz9UMw
Hi Anonymous ,
Thanks for amitchandak reply.
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:Click "Transform data" and open power query
Select the Title column and select the pivot column in the Transform toolbar, then in the screen that opens select Status in the value column and select the Don't Aggregate option in the Aggregate Value function.Select the Title column and select the pivot column in the Transform toolbar, then in the screen that opens select Status in the value column and select the Don't Aggregate option in the Aggregate Value function.Final output
You can also use this m code in advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZCxDsIgEIZfpWHuIvgC2g420cZYt6bDWS9KpGCAmvj2QtKhQAe7/Lk7/v/ycW1LrtwKJDk5ImiJOqvKWVPD4N8aC3Y0rij5hxuuJOnylhRq1AazjZtXpde9ujkt1PAWaPHu6jN8tRIitVO/FTQ8o8DhknqZ053gPUbeBgSa1L51egL98o3sJ/80Q8vlYx6hEXyQWKCnCX2QCPFpgh+YE34a889/u4jP1t2erbg9WwnP/oTvfg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Learner ID", type text}, {"Learner Name", type text}, {"Status", type text}, {"Division", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Title]), "Title", "Status") in #"Pivoted Column"Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous thanks, it doesnt work with "Status" as the completion dates are different so doesn't merge the IDs into one row. I had to add a custom column with the completion date which doesnt look as good.
Is there a way round this? I would like to keep the completion dates as they are useful later on for analysis, but in this instance I would rather it just said complete.
