Creating SCD Type 2 with discontinuous repeating combinations

[neatdot]

I have a dimension table as follows:

I am using the following transformation to create a SCD Type 2:

let
    Source = ...,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Col1", type text}, {"Col2", Int64.Type}, {"Col3", type text}, {"Year", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key", "Col1", "Col2", "Col3"}, {{"From Year", each List.Min([Year]), type number}, {"To Year", each List.Max([Year]), type number}})
in
    #"Grouped Rows"

This results in the following:

However, this is not correct. If you look at the original table, the combination of Col1 = "A", Col2 = 1, Col3 = "W" occurs in two periods, one from 2001 to 2002, and one from 2006 to 2006. These are being combined into a single row in the result above which overlaps with the from and to dates of the other records. How can I keep these as separate rows? In other words, I want to achieve this (correct) result instead:

Any ideas how I can amend the M code to do this? I want to do the transformation in Power Query, rather than DAX.

Many thanks

[artemus]

let
    Source = ...,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Col1", type text}, {"Col2", Int64.Type}, {"Col3", type text}, {"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "From Year", each Function.ScalarVector(type function(X as record) as number, each List.Accumulate([X], {} meta [Key=null, Col1=null, Col2=null, Col3=null], (current, next) => (current & {if Value.Metadata(current)[[Key], [Col1], [Col2], [Col3]] = next[[Key], [Col1], [Col2], [Col3]] then Value.Metadata(current)[Year] else next[Year]}) meta next))(_), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Key", "Col1", "Col2", "Col3", "From Year"}, {{"To Year", each List.Max([Year]), Int64.Type}})
in
    #"Grouped Rows"

Hopefully this works, I haven't tried Function.ScalarVector extensivly.

[neatdot · Accepted solution]

Works perfectly! Thanks, @artemus