cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Prodigy

## Splitting 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?

1 ACCEPTED SOLUTION
Super User

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!

10 REPLIES 10
Super User

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 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
Super User

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!

Anonymous
Not applicable

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}}),
#"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])],
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.

Post Prodigy

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

Anonymous
Not applicable

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

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

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

Anonymous
Not applicable

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}}),
#"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.

Post Prodigy

Hi @Anonymous

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?

Post Prodigy

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

Anonymous
Not applicable

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.

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

Feel free to connect with me:

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors