Forum Discussion
Obtaining unique values based on a given condition
hi tom1tas ,
create a blank query and in the advanced editor , copy paste the following code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRckrMA8Ji5/zcgsS8SqCAORAb6Rua6BsZGBkpxerAFIJUlJakFiEpNSSkVAGh1gyi1hRdLdR+BQxTMVSWJwKNxK0uFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, Company = _t, #"Working Hours" = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Company", type text}, {"Working Hours", Int64.Type}, {"Date", type date}}),
#"Grouped Rows by Employee and Date" = Table.Group(#"Changed Type", {"Employee ID", "Date"}, {{"Details", each _, type table [Employee ID=nullable number, Company=nullable text, Working Hours=nullable number, Date=nullable date]}}),
#"Added Index Column in the Details Column" = Table.AddColumn(#"Grouped Rows by Employee and Date", "Custom", each Table.AddIndexColumn([Details],"Index")),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Index Column in the Details Column", "Custom", {"Company", "Index"}, {"Company", "Index"}),
#"Take Value with Most hours worked" = Table.SelectRows(#"Expanded Custom", each ([Index] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Take Value with Most hours worked",{"Details", "Index"})
in
#"Removed Columns"
,
Anonymous tom1tas ,
So first two steps don't really matter ( source and change type)
After that:
1. Grouped rows by employee id and date aggregating "details" as all the rows which will give a table with all rows for that employee id and date.
2. Added an index column called "Custom" in this "Details" table
3. Expand Company, hours worked and index column
4. Filter index for 0. ( First row contains the record with MAX working hours) .
Hope this helps.
Hey sincerely thanks for your response, I am at the point where you group by employee id and dates and include all rows with no summarization, I am not sure when to index as I tried doing it after and before expanding and it does not keep my max company hours as you stated,
let me know how to proceed from here,
thanks kindly
Can you explain it as query steps? I used dummy data therefore it does not work for me to get m code solutions
