Turn rows into columns

[annag815]

Hi everyone, 

Currently my data looks like this: 

I need it to look like this: 

How would I accomplish this? 

[performl]

Hi there, 

I have a similar problem except my dataset is incomplete. 

As you can see in the picture below, each row in the third column (Registration Group) should be its own column against each row in the first column (Registered Provider Name) - except where a registered provider (colmn 1) has multiple registration groups (colmn 3), colmn 1 is empty / vacant. 

So I think to be able to use the pivot function I need to somehow populate each row in colmn 1 with the correct registered provider name, but is there an easy way to do this? 

Many thanks in advance.

[PhilipTreacy · Accepted solution]

Hi @annag815 

Download PBIX file with solution

In the Power Query Editor (click on Transform data in the PBI Ribbon) , select the Question and Answer columns then select Pivot Column from the Transform tab

In the Pivot Column dialog box choose Answer as the Value column and in Advanced options set the aggregate type to Don't Aggregate

Which gives this result

Here's the code which is in the PBIX file I linked to above

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrUg5IKjnnF5alFSrE6MAkjCIkpYQwhkSWMcBllhMsoI6xGxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Inspection = _t, Question = _t, Answer = _t]),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Question]), "Question", "Answer")
in
    #"Pivoted Column"

Regards

Phil

[annag815]

Thank you!