Generate list of years between two dates

[DataVitalizer]

Hi Community,

From this table structure

Category	Date1	Date2
CAT-A	25/06/2022	12/07/2026
CAT-B	2/1/2023	15/10/2025

I want to add a new column (Years) that contains a serie of all years between the date1 and date2

Category	Date1	Date2	Years
CAT-A	25/06/2021	12/07/2026	2021,2022,2023,2024,2025,2026
CAT-B	2/1/2023	15/10/2025	2022,2023,2024,2025

Any suggestions?

Thank you in advance

[Ahmedx]

fnd try this

Years = 
VAR _Date = GENERATESERIES(YEAR([Date1]),YEAR([Date2]) ) 
RETURN
    CONCATENATEX ( _Date, [Value], ",", [Value], ASC )

[Ahmedx]

in powe query

Text.Combine(
List.Transform(
List.Generate( ()=> [Date1],
 (x) => x<= [Date2],
 (x)=> Date.AddYears( x,1)),
          (y)=> Text.From (Date.Year(y))),", ")
-------------------------------
or 
Text.Combine( List.Transform(
{Date.Year([Date1])..Date.Year([Date2])},
    (y)=> Text.From(y)),", ")

[HotChilli · Accepted solution]

I think the example is not accurate but I get the idea.

In Power Query, add a custom column:

{ Date.Year([Date1])..Date.Year([Date2])}

This will create a list which you can expand from the interface (choose Extract Values then a comma for the separator)

[Ahmedx]

pls try this

Years = 
VAR _Date =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS ( CALENDAR ( [Date1], [Date2] ), "Year", YEAR ( [Date] ) ),
            "@YEAR", [Year]
        )
    )
RETURN
    CONCATENATEX ( _Date, [@YEAR], ",", [@YEAR], ASC )