Forum Discussion
Visualizing Historical Data
Hi tom-lenzmeier ,
In my example, I used the IsCurrent flag in the table to determine the version. I added a column where IsCurrent = 1 is labeled as Version 1 (current record), and IsCurrent = 0 as Version 0 (historical record).
I also transformed the data in Power Query by unpivoting columns like StartDate, EndDate, CurrentPrice, and PriceValidFrom, so these attributes are displayed as rows.
For the matrix visual, I set:
Rows: Attributes
Columns: Version
Values: AttributeValue
I disabled row and column subtotals so only the values appear side by side in the matrix.
Since your table already uses the SCD Type 2 structure (StartDate, EndDate, IsCurrent) as described in the documentation, this approach should work for you as well.
If you need more details, just let me know.
Hi tom-lenzmeier ,
In my example, I used the IsCurrent flag in the table to determine the version. I added a column where IsCurrent = 1 is labeled as Version 1 (current record), and IsCurrent = 0 as Version 0 (historical record).
I also transformed the data in Power Query by unpivoting columns like StartDate, EndDate, CurrentPrice, and PriceValidFrom, so these attributes are displayed as rows.
For the matrix visual, I set:
Rows: Attributes
Columns: Version
Values: AttributeValue
I disabled row and column subtotals so only the values appear side by side in the matrix.
Since your table already uses the SCD Type 2 structure (StartDate, EndDate, IsCurrent) as described in the documentation, this approach should work for you as well.
If you need more details, just let me know.
Hopefully, one last question. Should the unpivot action be the last step in the process? In other words, having already identified changes (additions/updates, etc.) and flag rows as current and not, then unpivot for purposes of the PowerBi visual?
Usually, the unpivot step comes toward the end of the process. The SCD Type 2 logic is used first to handle changes and ensure the table is current, using fields like StartDate, EndDate, and IsCurrent.
Once the data is set, you can unpivot the attribute columns in Power Query for the Power BI visual, which lets the attributes show as rows and the versions as columns in the matrix.
Unpivoting is generally for reporting or visualization purposes, not for the main SCD processing.
Thanks.