Excel how to merge duplicate rows into a single row with additional columns?

Question

I need help formatting my data as shown in the image below. These are only 3 columns, I have so many of these. How can I format this.

Current State

Desired State

For a context, my dataset has 2100 rows they should be around 600.

<table>
  <tr>
    <th>Number</th>
    <th>Category</th>
    <th>Comedian / Band / Guest / Regular / Performer / Fact</th>
  </tr>
  <tr>
    <td>1</td>
    <td>Band</td>
    <td>1. The Iron Patriot</td>
  </tr>
  <tr>
    <td></td>
    <td>Guests</td>
    <td>1. David Taylor</td>
  </tr>
  <tr>
    <td>2</td>
    <td>Band</td>
    <td>1. The Iron Patriot</td>
  </tr>
  <tr>
    <td></td>
    <td>Bucket Pulls</td>
    <td>2. Sean Dunn</td>
  </tr>
  <tr>
    <td></td>
    <td>Guests</td>
    <td>1. Sam Tripoli</td>
  </tr>
  <tr>
    <td></td>
    <td>Regulars</td>
    <td>1. Sara Mostajabi</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Band</td>
    <td>1. The Iron Patriot</td>
  </tr>
  <tr>
    <td></td>
    <td>Guests</td>
    <td>1. Kevin Christy</td>
  </tr>
  <tr>
    <td></td>
    <td>Regulars</td>
    <td>1. Sara Mostajabi</td>
  </tr>
</table>

This is the dataset in current state.

Michal · Accepted Answer · 2024-09-10 22:01:54Z

1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillDown = Table.FillDown(Source,{"Number"}),
    Pivot = Table.Pivot(FillDown, List.Distinct(FillDown[Category]), "Category", "Comedian/Band/Guest/Regular/Performer/Fact")
in
    Pivot

answered Sep 10, 2024 at 22:01

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saad Over a year ago

I get this error: Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=[Type]

Michal Over a year ago

@Saad - your input table must be different to the one you have posted above and it contains some NULL values. I have literally used your code snippet to create the input for my query.

Saad Over a year ago

It worked, there was some issue in "Number" column with empty cells which I still don't know but I tweaked the dataset a bit and your code gave right solution. Thanks a lot!

Collectives™ on Stack Overflow

Excel how to merge duplicate rows into a single row with additional columns?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related