Find the last row from duplicates and identify - Power Query

[Anonymous]

Hello,

Hoping someone is able to help me here, I have been unable to find a solution wherever I look.

Background

I have merged 3 tables together which has produced some 'duplicate' rows where there is a common Key but there are different details in other columns.

See Example:

Problem

I am looking to add a new column which will look at these rows for each duplicated Key (Changelogs Issue Key in the example above) and identify which is the last update in the 'Change To' column and place a '1' here. This is so a can do an accurate summary of issues in the report view.

Hopefully someone can help.

Thanks!

[Anonymous · Accepted solution]

I was able to solve this by combining three new columns in the table.

The first one finds the last row of each issue based on the date/time:

Is Latest Row = 
VAR LatestDate = MAXX(FILTER('Jira Merged','Jira Merged'[Issue Key] = EARLIER('Jira Merged'[Issue Key])),'Jira Merged'[Change Created])
RETURN IF('Jira Merged'[Change Created]=LatestDate,1,0)

The second one checks to see which is the latest sprint:

Is Latest Sprint = 
VAR LatestSprintDate = MAXX(FILTER('Jira Merged','Jira Merged'[Issue Key] = EARLIER('Jira Merged'[Issue Key])),'Jira Merged'[Sprint.startDate])
RETURN IF('Jira Merged'[Sprint.startDate]=LatestSprintDate,1,0)

And then the final one checks to see if these two columns both produce a '1' and if so then flags this as the last change in the final sprint:

Last Change last Sprint = 
IF('Jira Merged'[Is Latest Row]=1 && 'Jira Merged'[Is Latest Sprint]=1,1,0)

I am sure there are more efficient ways to complete this task and if so, I'd be happy to hear them!

[amitchandak]

@Anonymous , I think for this you need to create a new aggregate table with max date and merge it back and check for column values for flag

refer :https://www.myonlinetraininghub.com/excel-power-query-vlookup

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

[Anonymous]

Thanks @amitchandak , I'll check it out!