Forum Discussion

neatdot's avatar
neatdot
Helper I
6 years ago
Solved

Creating SCD Type 2 with discontinuous repeating combinations

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

Need Help

2 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago
    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.