Remove older overlaping date periods

[dalavouras]

Hi
I have a query in Power Query like:
Site id | Start Date | End Date | Value 1 | Value 2 | Value 3 |

The date periods are random and dataset comes every month as a txt file. Newer txt often contain corrections of older ones but do not necessarily share start date or end date. My goal is to keep the latest records and split the older ones to fill whatever remains (still newer should come first) and change the values 1 etc. according to the split date span.
Example:
Older record:

Site Id	Start Date	End Date	Value 1	Value 2	Value 3
580	02/01/2020	21/01/2020	10	20	30
656	15/01/2020	05/03/2020	65	30	45
689	01/01/2020	31/01/2020	50	80	60​

new record:

Site Id	Start Date	End Date	Value 1	Value 2	Value 3
580	12/01/2020	30/02/2020	20	30	50
888	03/02/2020	15/03/2020	32	75	60
689	01/02/2020	29/02/2020	60	70	30​

then keep the new one as is and make the older like:
Older record:

Site Id	Start Date	End Date	Value 1	Value 2	Value 3
580	02/01/2020	11/02/2020	=(10/20)*10=5	=(10/20)*20	=(10/20)*30
656	15/01/2020	05/03/2020	65	30	45
689	01/01/2020	31/01/2020	50	80	60​