Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Thank you Sir, this worked. Had to do some spin to my coding and it finally got to the results am looking for 🙂
You are welcome.
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
Hi MahyarTF,
Problem is, I have multiple columns other than those two. When this is done the other columns disappear.
Thanks,
Mobafa
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.
Hi,
You can use the Table.Max() "M" language function. To get mre help, share data in an MS Excel formatted table.
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
Hi,
Upload the file to Google Drive/One Drive and share the download link here.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |