cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Splitting overlapping dates using Power Query

I have the below table:

 

Start Date of ContractEnd Date of ContractContract
1/7/201931/8/2019X
1/9/201931/3/2020X
12/5/202031/12/2021X
1/4/201731/3/2018Y
1/4/201831/3/2020Y
11/5/202031/12/2021Y

 

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

 

Start Date of ContractEnd Date of ContractContract
1/7/201931/12/2019X
1/9/201931/12/2019X
1/1/202031/3/2020X
12/5/202031/12/2020X
1/1/202131/12/2021X

 

Similarly for contract Y. Is this possible?

 

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

1 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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

10 REPLIES 10
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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

 

 

 

 

image.png

 

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.

 

 

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

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:

 

image.png

 

this is the screen of function (ver 2)

image.png

 

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

 

image.png

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

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.

 

image.png

 

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.

 

 

 

Hi @Rocco_sprmnt21 

 

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?

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

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.

Super User I
Super User I

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



Feel free to connect with me:
LinkedIn

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors