cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Creating SCD Type 2 with discontinuous repeating combinations

I have a dimension table as follows:

 

neatdot_0-1593701875328.png

 

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:

 

neatdot_1-1593702099058.png

 

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:

 

2020-07-02_16-08-45.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Creating SCD Type 2 with discontinuous repeating combinations

Works perfectly! Thanks, @artemus 

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Creating SCD Type 2 with discontinuous repeating combinations

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.

Highlighted
Frequent Visitor

Re: Creating SCD Type 2 with discontinuous repeating combinations

Works perfectly! Thanks, @artemus 

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors