Help Converting SCD0 to SCD2

[ct_tm]

Hello!

I have been using an attribute table that utilizes a slowly changing dimension type 0 model where all the data is today's values, no history. My company has now turned on tracking of historical attributes in the system so that I can update our reports to track who was where, when, and other attributes in the same manor.

There are roughly 24 attributes that are now being tracked but for ease of the question I will only include VP and Regional Manager. But the solution needs to ba able to handle any number of attributes. 

Here is a small sample of how I received the SCD0 data:

Property Code	VP	Regional Manager
117	Judy	Chris
129	Warren	Pam
140	Tomeka	Debra
141	Warren	Lisa
143	Judy	Lynette
144	Warren	Cindy

But I receive the historical data as a single attribute name and value per row with effective and modified dates.

PropertyCode	AttributeEffectiveDate	AttributeLastModified	AttributeName	AttributeValue
117	1/1/2017	NULL	VP	Judy
117	12/1/2022	12/1/2022	Regional Manager	Chris
129	1/1/2017	NULL	Regional Manager	Pam
129	1/1/2017	NULL	VP	Warren
140	1/1/2017	NULL	VP	Tomeka
140	1/1/2017	NULL	Regional Manager	Debra
141	1/1/2017	NULL	Regional Manager	Lisa
141	1/1/2017	NULL	VP	Warren
143	1/1/2017	NULL	VP	Judy
143	1/1/2017	NULL	Regional Manager	Lynette
144	1/1/2017	NULL	VP	Warren
144	1/1/2017	NULL	Regional Manager	Cindy

I don't have direct access to the database, and our company does not employ a person in a traditional DBA type role. So, this solution needs to be done in PowerQuery and I have tried but once I start unpivoting or transposing I quickly make a mess of things.

How would one go about taking the new historical feed of data with 24 attributes being fed now as one row per attribute and turn it into a table that is laid out horizontally by effective date and property number that also fills down previous values that are unchanged, but updates in the row the value(s) that do change by date that they changed and can act as a historical slicer and leverage relationships in the report design instead of having to write complex DAX to write date based lookups for measures in the report?

I came across a forum question similar to this but the answers led me to two websites that didn't exactly answer my personal question. The sites were: Slowly Changing Dimension SCD Power BI Lookup table data warehouse (p3adaptive.com) and SCD Implementation with Temporal Tables in Power BI - Zartis.If the answer is in those 2 sites, please let me know because they didn't get me where I needed to go.

Thanks!

[ct_tm]

As nobody has bitten yet, I continue to look into this issue.

It seems like I should be able to group the items by PropertyCode, sorted by effective date ascending, and fill down the columns contained within the group then expand the grouped data but that doesn't appear to be working. All of my missing values are null, as I saw in some research that they need to be null in order to work properly. Some other solutions I saw created a new column for each item to be filled, but I have 24 columns currently with the potential of more to be added at any given time so the solution needs to deal with n number of columns in 1 step.

Still looking for some assistance here!

[ct_tm]

I worked with this a little further today since posting the thread because just typing it out was a little bit of a nice rubber duck session and I got to the point where I have figured out how to actually pivot this into kind of the right place but now I need to fill down empty values where the previous value did not change.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFNC4JAEIb/Suw5qN2E6BjZJUwk+jiIhzHHWtLdWMfAf59ZQdYmXnaHmedhX3bCkAVGX9FQtdAJsiGbExkZl4TLNMUjyRu6QK2BBwWtdSJTicln34e8xe0hK5FFw5BxPq0HfMRHYtyU/s7z6msf1MeqTKpPSjSYEF/1Bk9SK8gGa1BwQlO3Fmcji6cqZtYHLFIAeafSZDqAMaienDP+z211jhfo5CwRXIzNW+J9JU8W3c5v8EmfT/9D2RJUColeG3WcniHsnG2bUj0yRXc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"PropertyCode", Int64.Type}, {"AttributeEffectiveDate", type date}, {"AttributeLastModified", type text}, {"AttributeName", type text}, {"AttributeValue", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"AttributeEffectiveDate", "PropertyCode"}, {{"Partition", each _, type table [PropertyCode=nullable number, AttributeEffectiveDate=nullable date, AttributeLastModified=nullable text, AttributeName=nullable text, AttributeValue=nullable text]}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"AttributeLastModified", "AttributeName", "AttributeValue"}, {"AttributeLastModified", "AttributeName", "AttributeValue"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[AttributeName]), "AttributeName", "AttributeValue"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"PropertyCode", Order.Ascending}, {"AttributeEffectiveDate", Order.Ascending}})
in
    #"Sorted Rows"

So what I need next is a conditional fill down when the property code is the same, in this case Judy in the VP column needs filled down but only because it's the same property code (117). In the larger data set there will be lots of nulls after values that would not get filled down because there is no previous value for that property.

Also, the same point applies to then how would I use this in the relational diagram to leverage a relationship between the date table/slicer and this new table to automatically handle the history so there is no need to write in extra code to all my DAX measures.

Thanks!