Help Needed - Dataflow query to retain the latest record

Hi New_worker ,

Thank you for reaching out to the Microsoft Community Forum.

For each unique CustomerID, retain only the latest record based on versiondate.

Do not load new columns into the semantic model (meaning the output should contain only CustomerID, customername, and versiondate).

Correct Sequence:

1. Group by CustomerID, use "All Rows" operation, and calculate the max version date per customer ID:
This creates a nested table for each CustomerID, with a new column (say AllData) holding all their records. You also calculate MaxDate in the grouping step. This sets up what you need for filtering in the next step.

2. Expand the All Rows details column:
This brings back the original rows. Yes, it includes CustomerID again, but that's okay Power Query deduplicates repeated data, and the instruction isn't asking you to omit columns — just not to add new ones to the semantic model. This step is required to get the full data rows back, so you can filter.

3. Filter the query where the version date value equals the max version date value: Now you're working with full rows again, so you can filter only the latest version using the MaxDate column. This gives you one row per customer with the most recent versiondate.

4. Remove the max version date column:
This is crucial to meet the requirement: don’t add new columns to the semantic model. The MaxDate was just an intermediate value. You now have only the original columns: CustomerID, customername, versiondate.

Your Concern About Step 2:

"It will expand CustomerID as well", Yes, and that’s okay. The key is that in the end, you’re not adding new columns to the final output — you're just using intermediate steps to filter. After step 4, the output matches the original schema.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you