LOOKUP multiple rows of data into one single row.

[TanLC]

Hi, in below table, there are multiple rows / layers of Product Name and to be consolidate according to the main Product Name.

Eg: 2N123A with the Date of 12/12/2024 02:11:58 PM to be lookup to 123A as they can be linked by 1N123A, 13/12/2024 04:15:31 PM and so on.

I've tried using the method of LOOKUP and created quite some columns manually.

Any other alternate method to linked the columns automatically? 

Before:

Product Name	Production Date	Name 1	Date 1
123A	15/12/2024 03:17:25 AM	1N123A	13/12/2024 04:15:31 PM
1N123A	13/12/2024 04:15:31 PM	2N123A	12/12/2024 02:11:58 PM
2N123A	12/12/2024 02:11:58 PM	3N123A	10/12/2024 09:00:05 AM
789C	03/01/2025 05:08:07 AM	1N789C	31/12/2024 10:55:09 AM
1N789C	31/12/2024 10:55:09 AM	2N789C	31/12/2024 01:05:08 AM
2N789C	31/12/2024 01:05:08 AM	3N789C	29/12/2024 09:10:58 PM
3N789C	29/12/2024 09:10:58 PM	B030A	28/12/2024 11:12:24 AM

After:

Product Name	Production Date	Name 1	Date 1	Name 2	Date 2	Name 3	Date 3	Name 4	Date 4
123A	15/12/2024 03:17:25 AM	1N123A	13/12/2024 04:15:31 PM	2N123A	12/12/2024 02:11:58 PM	3N123A	10/12/2024 09:00:05 AM
789C	03/01/2025 05:08:07 AM	1N789C	31/12/2024 10:55:09 AM	2N789C	31/12/2024 01:05:08 AM	3N789C	29/12/2024 09:10:58 PM	B030A	28/12/2024 11:12:24 AM

Thank you.

Regards,

TanLC

[ThxAlot]

Transform your original dataset correctly, then drag-and-drop is enough.

[TanLC]

Hi @ThxAlot , thanks for the solutions. Possible to telI how to create the Steps for Prod, Sub & Upstream in Power Query?

Regards,

TanLC

[123abc]

plz can u sahre the sample pbix file.