Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.