Split rows based on start & end date

[CSRiotech]

Suppose I have following data: 

Product	Start Date	End Date
1	31 October 2021	25 December 2021
2	31 October 2021	10 January 2022
3	21 October 2021	15 February 2023

But I want to split the rows in which time period between 'Start Date' & 'End Date' spans more than one year(They are recognised Date variables). The table above would then be changed to:

Product	Start Date	End Date
1	31 October 2021	25 December 2021
2	31 October 2021	31 December 2021
2	1 January 2022	10 January 2022
3	21 October 2021	31 December 2021
3	1 January 2022	31 December 2022
3	1 January 2023	15 February 2023

How can I do this in powerBI? Any help would be much appriciated! 

Best regards

[AntrikshSharma · Accepted solution]

@CSRiotech You can use this:

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMlTSUTI2VPBPLslPSi1SMDIwAokYmSq4pCan5sKFYnWilYywKjU0UPBKzCtNLKoEiRiBVRqDjMBUaarglppUBFNqrBQbCwA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Product = _t, #"Start Date" = _t, #"End Date" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        { { "Product", Int64.Type }, { "Start Date", type date }, { "End Date", type date } }
    ),
    Result = Table.AddColumn (
        Source,
        "Repeat",
        ( CurrentRow ) =>
            let
                YearCount = 
                    Date.Year ( CurrentRow[End Date] )
                        - Date.Year ( CurrentRow[Start Date] )
                        + 1,
                GenerateDates = 
                    List.Generate (
                        () =>  [
                            Start = CurrentRow[Start Date],
                            End   = List.Min ( { CurrentRow[End Date], Date.EndOfYear ( Start ) } ),
                            Stop  = CurrentRow[End Date]
                        ],
                        each [Start] <= [Stop],
                        each [
                            Start = Date.StartOfYear ( Date.AddYears ( [Start], 1 ) ),
                            End   = List.Min ( { [Stop], Date.EndOfYear ( Start ) } ),
                            Stop  = [Stop]
                        ],
                        each { CurrentRow[Course], [Start], [End]  }
                    ),
                Result = 
                    Table.FromRows ( 
                        GenerateDates, 
                        type table [Course = text, Start Date = date, End Date = date] 
                    )
            in
                Result,
        type table
    ),
    Combined = Table.Combine ( Result[Repeat] )
in
    Combined