Power Query: Merge Rows without losing data

Question

Hello Excel-Community,

I am working on a query and need to process 45.000 rows. (~4.000 ID's and ~41.000 duplicates)

The following is the current problem:

I have multiple IDs, because every Step generated a duplicate with its date-information. Manually I transfered the rows to columns with the date-information below. (pivoting didn't work and totally messed up all other columns):

Now I am looking for a solution to merge all duplicates which also includes, that all date-information "override" the empty cells, so that I have all date-information in one single ID/row.

The wished outcome would be:

Could you please help me out here? I tried everything I found on google, but unfortunetly nothing really worked.

I tried...

... Duplicate -> Transpone -> merge -> transpose back -> append as new

... everything with new columns

thanks a lot.

Greetings

Manuel

Answer 1

Hola Manuel

You may try the following steps

Once uploaded the table to Power Query

1. Select the "Step" column/field
2. Go to the Transform tab
3. Select/click Pivot columns
4. On the Pivot Column window select "Date" as the Value column. On the Advanced Options select "Don't aggregate" from the dropdown and click OK

Note:

We added more data to the given sample table to better show how it works, also considering that some IDs might not have all the Steps completed.

RESULTS

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi Jeovany,

thanks for the help. It worked and was easier then I thought :D :) Thanks a lot!!!

I really appreciate your help.

Answer 3

Hi Jeovany,

thanks for the help. It worked and was easier then I thought :D :) Thanks a lot!!!

I really appreciate your help.

You are welcome,

I'm glad it helped you. Thanks for the positive feedback.

Regards