Powerquery Count days per year between two dates-CodePudding

In Power Query, I have two dates that I would like to get the duration (in days), but split per year as a new column. Columns would be dynamically added depending on the min and max dates.

Ideal outcome:

= let yearRange = Number.RoundUp(Value.Divide(Duration.Days(List.Max(#"Changed Type1"[End_Date]) - List.Min(#"Changed Type1"[Start_Date])), 365)),

minYear = Date.Year(List.Min(#"Changed Type1"[Start_Date])),

maxYear = Date.Year(List.Max(#"Changed Type1"[End_Date])) in

List.Accumulate(List.Numbers(minYear, yearRange), #"Changed Type1", (state, current) => Table.AddColumn(state, "Days_" & Number.ToText(current), each Duration.Days(Date.EndOfYear(Date.FromText(Number.ToText(current) & "/01/01")) - [Start_Date])))

This is what I have so far, but the problem is that it will always subtract the original Start_Date (the days duration becomes cumulative as the years go on).

CodePudding user response：

try below in powerquery

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start_Date", type date}, {"End_Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "year", each  List.Transform({Number.From([Start_Date])..Number.From([End_Date])}, each Date.Year(Date.From(_)))),
#"Expanded year" = Table.ExpandListColumn(#"Added Custom", "year"),
#"Added Custom1" = Table.AddColumn(#"Expanded year", "Count", each 1),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom1", {{"year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom1", {{"year", type text}}, "en-US")[year]), "year", "Count", List.Sum)
in  #"Pivoted Column"

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start_Date", type date}, {"End_Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "year", each {Date.Year([Start_Date])..Date.Year([End_Date])}),
#"Expanded year" = Table.ExpandListColumn(#"Added Custom", "year"),
#"Added Custom1" = Table.AddColumn(#"Expanded year", "Custom", each 
    (if [year]=Date.Year([End_Date]) then Number.From([End_Date])-Number.From(#date([year],1,1)) 1 else 0 ) 
    (if [year]=Date.Year([Start_Date]) then Number.From(#date(Date.Year([Start_Date]),12,31))-Number.From([Start_Date]) 1 else 0 )   
    (if Date.Year([Start_Date])<>[year] and Date.Year([End_Date])<>[year] then 1 Number.From(#date([year],12,31))-Number.From(#date([year],1,1)) else 0 )
    ),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom1", {{"year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom1", {{"year", type text}}, "en-US")[year]), "year", "Custom")
in  #"Pivoted Column"

I think your sample data answers are wrong, but include or remove the 1's as you want