Reporting in Slowly Chaning Dimensions

Hi somnath6309 ,

To prepare the customer table for slowly changing dimension reporting, you need to duplicate each customer for the years 2007, 2008, and 2009. In Power Query, you do this by appending the customer table three times, each time adding a different year value. Here's the M code:

let
    Source = CustomerOriginal,
    Add2007 = Table.AddColumn(Source, "Year", each 2007),
    Add2008 = Table.AddColumn(Source, "Year", each 2008),
    Add2009 = Table.AddColumn(Source, "Year", each 2009),
    Combined = Table.Combine({Add2007, Add2008, Add2009})
in
    Combined

Next, you add the Current and Historical manager columns by merging the combined customer table with the Current Country Manager table on CountryRegion and Year = LastYear, and separately with the Historical Country Manager table on CountryRegion and Year. After each merge, expand only the Manager column and rename it to Current and Historical.

For the CustomerKey, rather than using random numbers that would regenerate on every refresh, a stable approach is to create an index column and concatenate it with the year to generate a unique identifier. First, add an index column:

WithIndex = Table.AddIndexColumn(Combined, "Index", 1, 1)

Then create the CustomerKey as a text field combining year and padded index:

AddCustomerKey = Table.AddColumn(WithIndex, "CustomerKey", each Text.From([Year]) & Text.PadStart(Text.From([Index]), 5, "0"))

This gives you unique keys like "200700001", "200800002", etc., which are stable and traceable.

In the Sales table, extract the year from OrderDateKey using the first four digits. This is safer and cleaner than integer division because it works consistently even if the column is stored as text. Here's how to do it:

Year = Text.Start(Text.From([OrderDateKey]), 4)

With the extracted year and the CustomerCode, you can merge the Sales table with the customer table to retrieve the correct CustomerKey for each transaction. This allows you to build a relationship between the modified customer table and the sales table, enabling accurate reporting by both current and historical managers.

Best regards,