Reporting in Slowly Chaning Dimensions

[somnath6309]

Hi,

Some times we have to do reporting with slowly Changing Dimensions. Assume, "Sales Managers" based on Customer's Continent get changed over years and there is a requirement to prepare the following report that shows both present and historical manages and sales amount by years :

The data model looks like following. However, the two tables Current Managers and Historical Managers based on Continent are not linked with any table. 

The snapshot of the content of Current Contry Managers is as follows:

The Snapshot of the contents of Historical Country Managers is as follows:

The rest of the tables are as usual as found in Contoso data model. Now pls take a look with Customer table in which some modifications has been made with M code however, the code has not been shared in Pbix file. 

The following modifications have been made in Customer Table:

01. The Customer Table primarily had Customer Code only and the same was a unique value. Each line item of customer table has been duplicated twice. Hence, there are total 3 lines for each customer.

02. This split of each customer was made for the years 2007, 2008 and 2009 ( Pls view the year column). 

03. The Columns named " Current" and "Historical" was generated using Merge Query based on Year and Country Region columns.

04. A random number was generated for Customer Key ( the last column in table) which is the unique value in the present table and the same was used to create relationship with Sales Table. 

05. Sales Table only contains Customer Key not the Customer Code. However, perhaps it had contained the Customer Code.

The Year Column at the end was generated using RELATED Function to grab the year column from Customer Table. Based on both the columns, Year and Customer Code a Merge query was done to import the Customer Key from Customer Table to Sales Table. 

However, the steps from 3 to 5 cannot be done if one cannot accomplish the task mentioned in Steps 1 and 2. Step 4 is also critical because a random unique number has to be created for each line items and the same is not an Index Column. 

Please let us know how the steps 1, 2 and 4 are to be accomplished. 

Have attached the link to file. Do not know whether it will open or not 🙂 

https://1drv.ms/u/c/9b5853c19a1e2017/EXc8WJeo2GJOvKnrifqCX80BzNb8i2CnMl7ArxVnp1g6dQ?e=wMgfY4 

Regards,

Somnath6309

[somnath6309 · Accepted solution]

Hi, @DataNinja777 
Thanks for your help. However, I am not very familier with M Code and hence will request you to attach a sample file ( however, figures are not required to be matched) so that we can understand how to use the codes. 

Moreover, pls use some lines to explain the following in some details:

01. what is the use of the "original" key word ? what does it mean to use it here?

02. Add2007, Add2008, Combined : these are variables, right ?

03. Pls let us know about Table.Combine command and use of Parentheses "()" and Curlybraces "{}" together. 

04. Pls give us some information about the "each" keyword in MCode. When it is to be used ?

However, will request you to pls let us know how did you learned M Code ? Can you pls share some tutotial links here ?

Thanks and Regards,

Somnath6309

[DataNinja777]

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,