Picking highest duration of duplicated values

[Anonymous]

Hello Power BI Community,

Am trying to find a way where I can pick the longest duration associated with duplicated traces in Power Query.

Hope someone could help, I've put some photos for illustration below where highlighted are the values to keep:

Thank you in advance,

Mobafa

[Anonymous]

Thank you Sir, this worked. Had to do some spin to my coding and it finally got to the results am looking for 🙂 

[Ashish_Mathur]

You are welcome.

[MahyarTF]

In Power Query use the 'Group By' in Home tab and select the 'Trace' column in first column and 'Max' in Operation, the 'Duration' column in column drop-down list :

Select Ok

Appreciate to Kudos

[Anonymous]

Hi MahyarTF,

Problem is, I have multiple columns other than those two. When this is done the other columns disappear.

Thanks,

Mobafa

[MahyarTF]

Hi,

Not sure what is your business and how you want to use the max duration,

But you could create a duplicated table (just on the 'Trace' and 'Duration' Column), and do the Group by on this copy table, then use the MaxDuration based on the Trace number in your visuals.

Again, not sure if it is useful for you.

Appreciate for Kudos.

[Ashish_Mathur]

Hi,

You can use the Table.Max() "M" language function.  To get mre help, share data in an MS Excel formatted table.

[Anonymous]

Hello Ashish,

Tried to attach a file sample for you to review but I guess it is not supported.

Could you advise of other possible methods to share? 

Thank you,

Mobafa

[Ashish_Mathur]

Hi,

Upload the file to Google Drive/One Drive and share the download link here.

[Anonymous]

Hi Ashish,

Here you go, file is uploaded to the below link:

https://www.dropbox.com/s/9n40tjvlqtkh0f0/Sample%20Data.csv?dl=0

Thank you,

Mobafa

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Trace", Int64.Type}, {"Target Action", type text}, {"Account Type", type text}, {"Date", type datetime}, {"Duration", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Trace"}, {{"Count", each Table.Max(_,"Duration")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Target Action", "Account Type", "Date", "Duration"}, {"Target Action", "Account Type", "Date", "Duration"})
in
    #"Expanded Count"

Hope this helps.