Overlapping dates in PowerQuery-CodePudding

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!

CodePudding user response：

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"

CodePudding user response：

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.

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
    newTable = Table.FromColumns(
        Table.ToColumns(#"Changed Type") & newcols,
        Table.ColumnNames(#"Changed Type") & List.Transform(Years, each "Days in " & Text.From(_))        
        ),
//Set data types
    typed = Table.TransformColumnTypes(newTable,
    List.Transform(List.Select(Table.ColumnNames(newTable), each _ <> "End Date" and _ <> "Start Date"), each {_, Int64.Type}))
in
  typed