Splitting overlapping dates using Power Query

[Kolumam]

I have the below table:

Start Date of Contract	End Date of Contract	Contract
1/7/2019	31/8/2019	X
1/9/2019	31/3/2020	X
12/5/2020	31/12/2021	X
1/4/2017	31/3/2018	Y
1/4/2018	31/3/2020	Y
11/5/2020	31/12/2021	Y

I want to split the overlapping dates for each contract like below:

Start Date of Contract	End Date of Contract	Contract
1/7/2019	31/12/2019	X
1/9/2019	31/12/2019	X
1/1/2020	31/3/2020	X
12/5/2020	31/12/2020	X
1/1/2021	31/12/2021	X

Similarly for contract Y. Is this possible?

@mow700 @Smauro @Mariusz @HotChilli @edhans @mahoneypat 

[edhans]

You are going to have to explain the logic of how to get from table 1 to table 2. I cannot see what you are doing.

Why did the July 1 start date have an ending date of 8/31  get changed to 12/31

Why did a new start date of Jan 1 appear in the start column? It wasn't in the first table?

Etc.

[ImkeF · Accepted solution]

Hi 

this is done in my solution here: https://community.powerbi.com/t5/Desktop/Need-urgent-help-DAX-calculation/m-p/1216539#M541668

(Assuming there is an error in the first row of the sample you've provided and it ends end of August and not end of year:

Start Date of Contract	End Date of Contract	Contract
1/7/2019	31/08/2019	X
1/9/2019	31/12/2019	X
1/1/2020	31/3/2020	X
12/5/2020	31/12/2020	X
1/1/2021	31/12/2021	X

[Anonymous]

here an attempt to interpret the request:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31zcyMLRU0lEyNtS3gLEjlGJ1QJKWSJLGQLaRAULSSN8UJgKUBXKBHEMkvSYgveYIvYYWQHYksqQFmsFQSUMcBgOlYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, Contract = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"end", type date}, {"start", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each splitRow(_)),
    #"Expanded Custom" = Table.FromRecords( Table.ExpandListColumn(#"Added Custom", "Custom")[Custom]),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Custom", {"start"})
in
    #"Removed Errors"

where function spliRow is:

let
    splitRow=(dRow)=>
    let 
        startY=Date.Year(dRow[start]),
        endY=Date.Year(dRow[end]),
        nrows=endY-startY,
        splitRows=if nrows >0 then
        let
        row= Record.TransformFields(dRow,{{"start", DateTime.From},{"end", DateTime.From}}),
        fRow=row & [start=Date.From(row[start]),end=Date.From(Date.EndOfYear(row[start]))],
        lRow=row & [start=Date.From(Date.StartOfYear(row[end])),end=Date.From(row[end])], 
        bdate=List.Transform({1..nrows-1},each {Date.AddYears(#date(endY,1,1),_-nrows),Date.AddYears(#date(startY,12,31),_)}),
        bRows= List.Accumulate({0..nrows-2},{},(s,c)=>s&{dRow&[start=bdate{c}{0}, end=bdate{c}{1}]}) 

        in {fRow} & bRows & {lRow}
        else {dRow}
in splitRows

in 
splitRow

I think the solution is partial.
In the sense that  @Kolumam  also wants that if the end date of a row is contiguous or higher than the start date of the next row, that they are treated as a union.

[Kolumam]

@Anonymous Can you guide me how to invoke the function in Power Query?

[Anonymous]

Let we try,.

I don't know where you encounter difficulties, so I explain in general how it works.

You can use as i did, as function inside add. column function

Table.AddColumn(#"Changed Type", "Custom", each splitRow(_))

 the function expects a record as input parameter. The record must have the start (type date) and end (type date) fields and other generic fields.

The function  has been designed for the precise scenario that you have presented, for this reason it does not do many checks.

I take this opportunity to give a more terse version, I take this opportunity to give a more succinct version, but that does exactly the same things.

let
    splitRow=(dRow)=>
    let 
        startY=Date.Year(dRow[start]),
        endY=Date.Year(dRow[end]),
        nrows=endY-startY,
        splitRows=if nrows >0 then
        let
        row= Record.TransformFields(dRow,{{"start", DateTime.From},{"end", DateTime.From}}),
        fRow=row & [start=Date.From(row[start]),end=Date.From(Date.EndOfYear(row[start]))],
        lRow=row & [start=Date.From(Date.StartOfYear(row[end])),end=Date.From(row[end])], 
        bRows=List.Transform({1..nrows-1},each 
                            dRow&[start=Date.AddYears(#date(endY,1,1),_-nrows),end=Date.AddYears(#date(startY,12,31),_)])
        in {fRow} & bRows & {lRow}
        else {dRow}
in splitRows

in 
splitRow

You should put these lines of code in a separate query called splitRow.

For example you could have this situation:

here the screen of source data:

this is the screen of function (ver 2)

and here the result tab of the query Table which has the code I already posted in the previuos message.:

I hope this clarify, a little bit more what is going on ...

[Anonymous]

Let suppose you have you table load in query Azzurro and the output of last step of the query Azzurro is the your table .

You can refer to that table from another query using the name of the query, like done in the following screen. 

You could actually use the name Azzurro directly in the red oval, without the intermediate step that makes use of the Source variable.

a second possibility, if you prefer, would be to use the following lines of code, inside the blue query, hanging them at the end of the existing code.

// attach after your code that loaad in the data.

    #"Changed Type" = Table.TransformColumnTypes(the_name_of_last_step,{{"end", type date}, {"start", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each splitRow(_)),
    #"Expanded Custom" = Table.FromRecords( Table.ExpandListColumn(#"Added Custom", "Custom")[Custom])
in
    #"Expanded Custom"

In this case the first parameter of Table.TransformColumnTypes(the_name_of_last_step 

must be replaced with the name of the previous step.

[Kolumam]

Hi @Anonymous 

Thank you so much!

Last question: If I use a table from my data source (say from Azure) how do I reference it in your power query instead of the custom table that you have created?

[Kolumam]

@Anonymous  your solution seems to be correct. This is exactly what I want. I made a mistake in the first row as indicated by @ImkeF 

[Anonymous]

ok.

I take this opportunity, just to make an observation and ask for comments.
The point concerns the use of the Date.EndOfYear function which seems to accept only DateTime type arguments and not Date.
For this reason in the spliRow function I have done back and forth dateTime.From and dateFrom.

[Smauro]

Could you explain why you'd like to see the second row in your result?