Remove the duplicate values without consolidating values

You can achieve the desired results by adding a couple of steps in query editor. Try these:

1. Add Conditional Column to replace total_sec values > 50000 with 50000 else total_sec value.

2. Add an Index Column.

3. Group By SessionId, choose Operation = All Rows. You will get a table for each session id.

4. Transform each row to add another index to all rows in the table (generated in step above). This will generate a groupIndex i.e. new indices per session id.

5. Expand this new column now.

6. Based on this new group index column, if its not equal to 1, replace values with null.

Hope that helps.

Here are the steps from advanced editor.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPNilsxDIVfpWQ9CpYsW/IjlC4KbekmhCL/QWA6A83tom9f35t0JjPuMnAX8rk+H/KxfDjsdg833/HhsDu38/n0/HSqQzgv9muptrTl9LONdXuqN6tP7c93e/y9lt+eF3v88bWVDcGtO8gxE2TWziGUHIKObeQBHZDD9AHjnuL/pS+fP7JXGVW84LB7UI4Gvos2cto79cnrcZb8FYe4/lS9F48j35OHGNMNj7iAiSYIEixGtVbKHJ+f4/PX+JBxJYcLzjsBLUWArbngM9aaaPLy3B7T9biEblSo9+Kh5xseYQfjmsBMxEvMUeocX5h5m7RNiw+v05LaSM+xgNWmPmCvNfh3Xtm797irtKZHRK/piSGkkASGmq030U5h9q7jSjwFsA0Lb5fLOtq4E5LEvUWiIGTPDrSW8UYTxZzy2OIQEC/+QXST8tKjW28k8YWm2QbNKwix9FjUkGz18os37bdQ3yr/Xi/rNi/R7Y7Hvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"sessionid", type text}, {"startdatetime", type text}, {"enddatetime", type text}, {"KeyValue", type text}, {"Total_Sec", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "custom_column", each if [Total_Sec] > 50000 then 50000 else [Total_Sec]),
AddedIndex = Table.AddIndexColumn(#"Added Conditional Column", "Index", 1, 1),
GroupBySessionId = Table.Group(AddedIndex, {"sessionid"}, {{"GroupBySession", each _, type table [sessionid=text, startdatetime=text, enddatetime=text, KeyValue=text, Total_Sec=number, custom_column=number, Index=number]}}),
AddGroupIndex = Table.TransformColumns(GroupBySessionId, {{"GroupBySession", each Table.AddIndexColumn(Table.Sort(_,"Index"), "GroupIndex",1), type table}}),
#"Expanded GroupBySession" = Table.ExpandTableColumn(AddGroupIndex, "GroupBySession", {"startdatetime", "enddatetime", "KeyValue", "Total_Sec", "custom_column", "Index", "GroupIndex"}, {"startdatetime", "enddatetime", "KeyValue", "Total_Sec", "custom_column", "Index", "GroupIndex"}),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded GroupBySession", "Custom", each if [GroupIndex] = 1 then [custom_column] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"custom_column", "Index", "GroupIndex"})
in
#"Removed Columns"

Hi chandu080 , 

You also could simplify code like below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZM7bgQhEESvYk3sXvUPujmCI0tOVxvwGe5/BDOz4/3hcDNUUE9NUZzPi64docTCUNS7hlBLCL58LixACIyUPiieOP4v/Xx/qbiNVVwunwNHXcA1ZpBuvjJ679wnr9AsyYEj2jbd38XTqO/kEcX0wGOtkM0TBAs5Rs9rrXN8MscnR3yktJHDFSdo4LUaaF4xSKHWEk9encdTPq7LhGNF/i4eiT7wmDpkbQlyNhOLJVqb4wszb5f2tki4tyWtIz1Ug9xWl0C9tSAvXjvhK+6QtvSY+Z6eZYIUksFQS+6reecwe7e6sk4B7GXR/XHVxxhvQrLhM5KMoIgieKut5cSxpDKOIAHR1T+IOCm3GXF7kaRXmpc8aOJgrNZj9UycN6/evOm0h/qs/P1e9b0vEZfL5Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [sessionid = _t, startdatetime = _t, enddatetime = _t, KeyValue = _t, Total_Sec = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"sessionid", type text}, {"startdatetime", type text}, {"enddatetime", type text}, {"KeyValue", type text}, {"Total_Sec", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"sessionid"}, {{"all", each _, type table [sessionid=text, startdatetime=text, enddatetime=text, KeyValue=text, Total_Sec=number, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "group id",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"startdatetime", "enddatetime", "KeyValue", "Total_Sec", "group id"}, {"startdatetime", "enddatetime", "KeyValue", "Total_Sec", "group id"}),
    Custom1 = Table.ReplaceValue( #"Expanded Custom", each [group id], each  if [group id]<>1 then null else if [Total_Sec]>5000 then 5000 else [Total_Sec], Replacer.ReplaceValue, {"group id"})
in
    Custom1

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I got the simple solution.

SingleTime = if('DE'[Index]=CALCULATE(min('DE'[Index]), ALLEXCEPT('DE', 'DE'[sessionid])), min(500000,IF(ISBLANK('DE'[Total_Work_Sec]),'DE'[Total_Sec],'DE'[Total_Work_Sec])), BLANK())

It worked. grouping tables will not work for dynamic data.