Forum Discussion
Post ProdigySplitting overlapping dates using Power Query
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?
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
10 Replies
- Smauro
Solution Sage
Could you explain why you'd like to see the second row in your result? - edhans
Community Champion
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
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 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 splitRowI 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.
