Help Needed - Dataflow query to retain the latest record

[New_worker]

Hi -

I am preparing exam and have below question but I am not clear about the answer. 

You have a Fabric workspace that contains a Dataflow Gen2 query. The query returns the following data.
customerID: whole number, Contains unique customer IDs
customername: text, Contains customer names
versiondate: date/time, contains record timestamps

You need to filter the results to ensure that only the latest version of each customer’s record is retained.
The solution must ensure that no new columns are loaded to the semantic model.
Which four actions should you perform in sequence in Power Query Editor?

Options:
Expand the All rows details column.
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 duplicates based on CustomerID.
Remove the max version date column.
Group by CustomerID, use all rows operation, and calculate the max version date per customer ID

I tried in power bi desktop and my answer is 

Group by CustomerID, use all rows operation, and calculate the max version date per customer ID

Expand the All rows details column.

Filter the query where the version date value equals the max version date value.

Remove the max version date column.

But for the step2, expand all rows details column, it will expand CustomerID as well, and option did not say only expand versiondate and customername. So, I am confused by the option..

Could anyone help clear the correct answer? Thanks. 

[v-dineshya · Accepted solution]

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

[rajendraongole1]

Hi @New_worker -Please find the below answers:

1.Group by CustomerID, use All Rows operation, and calculate the max version date per customer ID
Ans This creates a nested table of all rows per customer, with a column for max versiondate. Perfect as a starting point.

2.Expand the All rows details column
Ans This gives you all the original columns (CustomerID, customername, versiondate) for that customer, repeated per row. You’re right — when you expand it, CustomerID comes back too, but that's okay as long as you don’t load extra columns to the final model. You’ll remove the grouping output later.

3.Filter the query where the versiondate value equals the max version date value
Abs This keeps only the latest record per customer.
4.Remove the max version date column
Ans Required to keep the result clean and meet the requirement of not adding new columns.

Hope this helps.