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 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
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.
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 ...
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.