Forum Discussion
Post ProdigySplitting overlapping dates using Power Query
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
Community ChampionYou 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.
Community ChampionHi
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.
KolumamAnonymous 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.
- Kolumam
Anonymous 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 splitRowYou 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 ...