Merging multiple rows into one based on date values

[arnaudmanir]

Hello. I'm new to Power Query and in dire need of some help here.

I'm in the auto finance industry and working on a table with thousands of serial numbers, corresponding funding date and payout date (if the loan is closed), all three listed in separate columns. You may have different rows with the same serial number if the asset was funded more than once.

I want a list of each asset that was financed more than once with columns showing the serial number, first advance date, first payout date, second advance date, second payout day, etc.?

I managed to filter for the serial numbers but the part I'm struggling with is merging multiple rows into one as described above.

[ovde · Accepted solution]

Hi @arnaudmanir 

I think you want to look into using the table.group function. Table.Group - PowerQuery M | Microsoft Learn

When it comes to showing [second advance date], [second payout day] do you want to show these is separate columns? If so you might want to duplicate the relevant columns first and then use the group function with the correct aggregation to show the second or third etc. values. 

Hope this helps!

[arnaudmanir]

It helped immensely, thanks a bunch. I used min and max aggregate on the advance date column to filter for the initial and the latest advance date. To expand on that, how can I see dates in between those two dates? I know List.Min will show the first and List.Max the latter, but what about the in betweens? Hope I'm making sense.