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

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

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.

Solution

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