Forum Discussion

annag815's avatar
annag815
Frequent Visitor
5 years ago
Solved

Turn rows into columns

Hi everyone, 

 

Currently my data looks like this: 

 


I need it to look like this: 

 

How would I accomplish this? 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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

  • performl's avatar
    performl
    New Member
    5 years ago

    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.