Overlapping dates in PowerQuery

Question

So I have a table with StartDate and EndDate and I need to know if this dates overlap in a natural year. In example January 1st 2021 and December 31st 2021 and know how many days where in that period.

I have a formula that works in PowerBI that is:

Note: "DATA" is the name of the query.

OVERLAP days 2021 = MAX(MIN(DATE(2021,12,31),'DATA'[StartDate].[Date])-MAX(DATE(2021,1,1),'DATA'[EndDate].[Date])+1,0)

But now I need to translate it to PowerQuery.

Thanks!

horseyride · Answer

How about

let Source = #table({"StartDate", "EndDate"},{{#date(2021,5,31),#date(2021,7,31)}}),
year=2021,
#"Added Custom" = Table.AddColumn(Source, "Overlap", each 
    if [EndDate]<#date(year,1,1) or [StartDate]>#date(year,12,31) or [EndDate]<[StartDate] then null else 
    Number.From(if [EndDate]<#date(year,1,1) then #date(year,1,1) 
    else if [EndDate]>#date(year,12,31) then #date(year,12,31) 
    else [EndDate]) -
    Number.From(if [StartDate]<#date(year,1,1) then #date(year,1,1) 
    else if [StartDate]>#date(year,12,31) then #date(year,12,31)
    else [StartDate]))
in #"Added Custom"

Ron Rosenfeld · Answer

The basic algorithm is pretty much the same. But I confess I got a bit carried away with implementing it.

The problem is in how you want to define the years of interest, and how you want to display the result when there are multiple years.

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range or from within sheet
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

Here is a method using various M-Coding to develop

a list of all the years that are represented in the table
List.Generate to generate a list of days in any one Year
List.Accumulate to iterate through List.Generate for each year
Some other functions to create the table with the additional columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),

//add columns for each year
  //create list of years for the table
    Years = 
        let 
            firstYear = List.Transform(#"Changed Type"[Start Date], each Date.Year(_)),
            lastYear = List.Transform(#"Changed Type"[End Date], each Date.Year(_))
        in 
            List.Numbers(List.Min(firstYear), List.Max(lastYear) - List.Min(firstYear)+1),

//create lists of days count for each year, based on the start and end dates
newcols = List.Accumulate(Years, {}, (state, current) => state & 
    {let 
        ends = List.Transform(#"Changed Type"[End Date], each List.Min({#date(current,12,31), _})),
        starts = List.Transform(#"Changed Type"[Start Date], each List.Max({#date(current-1,12,31), _})),
        dys = List.Generate(
            ()=>[d=List.Max({0,Duration.Days(ends{0} - starts {0})}), idx=0],
            each [idx] < List.Count(ends),
            each [d=List.Max({0,Duration.Days(ends{[idx]+1} - starts {[idx]+1})}), idx=[idx]+1],
            each [d]
            )
    in 
        dys}),

//add the new columns to original table
    newColNames = List.Transform(Years, each "Days in " & Text.From(_)),
    newTable = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & newcols,
        Table.ColumnNames(#"Changed Type") & newColNames),
//Set data types
    typed = Table.TransformColumnTypes(newTable, List.Transform(newColNames, each {_, Int64.Type}))
    
in
  typed

Overlapping dates in PowerQuery

2 Answers