Merging rows with duplicate values

[Anonymous]

I have data in Power BI which has been obtained through an API linked to my companies LiveChat service. The data comes in as one row and each Agent and their category as a separate column. So there are 64 columns and 1 row.

I want to create a table with only 6 rows (one for each employee) and 9 columns (Name and 8 categories).

I have managed to Unpivot the columns, edit the names and remove null values but I'm still left with 48 rows (8 rows for every employee). I do have 9 columns, but there is only data in one cell per row.

I want the values to be as a list and no operations carried out.

I have tried following some suggestions from ChatGPT but I just seem to be going round in circles.

I'm sure there's a simple solution for this but I am relatively new to this program and I can't seem to figure this out.

Thanks

[Ahmedx]

The simplest solution is to look at all 8 columns and fill them in; except for the last ninth column, do not touch it.
You may first need to convert empty values to null

then filter the last column (column 9) removing null values

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[HotChilli]

Power Query-> use a Group By with aggregations on each column