cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GTPowerBIUser Frequent Visitor
Frequent Visitor

Pivot Text Columns by initial row

I'd like to pivot multiple text rows into one with a comma based on an employee column. Does anyone know how to do this?

 

I have the following table:

 

Employee ID

Employee Degree

 

It looks like this:

Emp ID   |   Degree

251        |   Master of Science

251        |   Bachelor of Arts

352        |   Bachelor of Sciences

 

I'd like it to look like:

 

Emp ID      Degree

251       |   Master of Science, Bachelor of Arts

352       |   Bachelor of Sciences

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M Established Member
Established Member

Re: Pivot Text Columns by initial row

In Power Query:

  1. Group the table by Emp ID and want all Rows:
  2. Groupby.png
  3. Add a custom column with this code:
List.Accumulate(
[Count][Degree],
"",
(state,current)=> (state &", "& current)
)
  1. It will produce a column like this:
  2. List Accum Column.png
  3. Remove the [Count] column
  4. Split the new Degree column by a comma, left most delimiter ( it will get rid of that inital comma)
  5. Remove the blank column
  6. Trim the degree column

FInal Table:

Final Table.png

3 REPLIES 3
Nick_M Established Member
Established Member

Re: Pivot Text Columns by initial row

In Power Query:

  1. Group the table by Emp ID and want all Rows:
  2. Groupby.png
  3. Add a custom column with this code:
List.Accumulate(
[Count][Degree],
"",
(state,current)=> (state &", "& current)
)
  1. It will produce a column like this:
  2. List Accum Column.png
  3. Remove the [Count] column
  4. Split the new Degree column by a comma, left most delimiter ( it will get rid of that inital comma)
  5. Remove the blank column
  6. Trim the degree column

FInal Table:

Final Table.png

GTPowerBIUser Frequent Visitor
Frequent Visitor

Re: Pivot Text Columns by initial row

This worked perfectly! Thank you!

Nick_M Established Member
Established Member

Re: Pivot Text Columns by initial row

Awesome! Glad it worked Smiley Happy