Forum Discussion

GeekMoore's avatar
GeekMoore
Frequent Visitor
1 year ago

Pivot csv to columns

I have a column of data showing which interventions a student has been in which is separated by commas.

 

 

I would like to convert this so that each item becomes a column header with a mark in the cell to indicate if the student is linked to that intervention

 

I'm not sure of the best way of approaching this.

I tried turning the text into columns separated by "," unpivoting that to get the table below, but that seems to remove students with no intervention and I can't figure out how to get it into the format I want.

 

Any suggestions on how to approach this would be apeaciated.

 

Need Help

1 Reply

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    Hi GeekMoore, 

     

    Try this

     

    • Select the Interventions column.
    • Go to Transform Tab → Split Column → By Delimiter.
    • Choose Comma (",") as the delimiter.
    • In the next step, select "Split into Rows" instead of columns.
    • Select the Interventions column.
    • Go to Transform Tab → Pivot Column.
    • In the "Values Column," choose Student (or any non-null column).
    • In the Advanced Options, choose "Don't Aggregate".

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!