Forum Discussion
neatdot
6 years agoHelper I
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(Sou...
artemus
6 years agoMicrosoft Employee
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.