Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GTPowerBIUser
Helper III
Helper III

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
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

This worked perfectly! Thank you!

Anonymous
Not applicable

Awesome! Glad it worked 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.