Forum Discussion

Kolumam's avatar
Kolumam
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

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 

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

10 Replies

  • Smauro's avatar
    Smauro
    Icon for Solution Sage rankSolution Sage
    6 years ago
    Could you explain why you'd like to see the second row in your result?
  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.