Power Query Merge Question

[Cmpunk]

Hello All,

Suppose I have two files PQ1 and PQ2 and data in PQ1 and PQ2 are as follows:

Legal Name	Deal	Origination Date	Maturity Date
TMT	D351	3/31/2016	3/31/2023

Deal	Legal Name	Origination Date	Maturity Date	Extended Maturity Date
D351	TMT	3/31/2016	3/31/2022	3/31/2023

When I use PQ and merge I will get following result

Legal Name	Deal	Origination Date	Maturity Date	Deal	Legal Name	Origination Date	Maturity Date	Extended Maturity Date
TMT	D351	3/31/2016	3/31/2023	D351	TMT	3/31/2016	3/31/2022	3/31/2023

Now As you can see that extension has place so result that I actually want to see is:

Legal Name	Deal	Origination Date	Maturity Date	Deal	Legal Name	Origination Date	Maturity Date
TMT	D351	3/31/2016	3/31/2023	D351	TMT	3/31/2016	3/31/2022
TMT	D351	3/31/2016	3/31/2023	D351	TMT	3/31/2022	3/31/2023

Could anyone advise how to get above result?

[#2]

@Cmpunk The attached file contains a PQ solution that matches your example, though I suspect that your real life data is not as simplistic as you projected it. So, it might work at all in the real world at all.

[#3]

Thanks Riny for your solution. I have one doubt when you added custom column and wrote following M-code: Table.AddColumn(#"Added Index", "OD2", each if [Maturity Date] <> [Extended Maturity Date] then [Origination Date] else #"Filled Down"[Maturity Date]{[Index]-1})

I am not familiar with M-language yet? Is there any other way to implement this logic?

Thank you for your response.

[#4 · Accepted solution]

@Cmpunk Can't judge how the logic could be altered without seeing some real data. Many transformations in PQ can be done by just clicking the correct buttons, but at some point in time you'll find yourself needing to write or edit M-code in order to achieve more complicated goals. So, it's inevitable that you have to start learning about it.