Comprehensive Detailed Explanation
We have a table with:
CustomerID → unique ID
CustomerName → customer’s name
VersionDate → timestamp representing version history
The requirement:
Keep only the latest record per customer .
Ensure no new technical columns (like max version date) remain in the semantic model.
Step 1: Identify latest version per customer
We need to determine the latest VersionDate for each CustomerID .
Use Group By CustomerID and calculate Max of VersionDate .
This creates a mapping of CustomerID → latest VersionDate.
Step 2: Filter to keep only latest records
Join or filter original data so that only rows with VersionDate = Max(VersionDate) are retained.
This ensures that only the most recent record is kept per customer.
Step 3: Remove the helper column
The column holding Max VersionDate is only needed for filtering.
It must be removed because the requirement is that no new columns are loaded to the semantic model.
Step 4: Remove duplicates
As a final safeguard, remove duplicates by CustomerID , ensuring that each customer appears only once.
Final Ordered Actions
Group by CustomerID and calculate the max version date per customer ID.
Filter the query where the version date value equals the max version date value.
Remove the max version date column.
Remove duplicates based on CustomerID.
