Moving Rows into columns with Unique ID

[Whibley]

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.

[v-heq-msft · Accepted solution]

Hi @Whibley ,
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

[Whibley92]

@v-heq-msft 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.

[amitchandak]

@Whibley , 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

[Whibley]

Unpivot seems to be putting data in rows rather than columns.

I want the row to group all courses in columns with the one unique ID.

To this:

Learner ID

Learner Name

Course 1

Course 2

Course 3

Division