Forum Discussion
Grouping by three conditions
- 5 years ago
No. As I said earlier, null, blank/empty, and space are different. Here is the same code but I've replaced your <space> with ""
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZG7DoJAEEV/ZbK1xczs8lg6Gwtb7QgFRqIkCAmPwr93ARMgA0qyJAS4h3Nn4lhpbdFYY9RBMcO5K4DR3YO7LtUra595+YCsaDL3gBgI3XtGoDBiG2lSyWHJwIlxfeYNfFkyikPUhoEh7dnAfYMGjt1j9/81RhQKCJutEqsAkgCeN0jb1QIuirLAMjqW/18CpQNZOGW3qYQU6I8U2JGz7sjJ+1NOilf1PS/T+i3lKWIpP4dtyuMowV6omXzfmnF1M4th8j+255qwLyHzEezcQU/yVJJ8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Record last updated", type datetime}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceValue,{"Status", "Result"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Record last updated=nullable datetime]}}), #"Added Status" = Table.AddColumn( #"Grouped Rows", "Status", each Table.Max( Table.FromRecords( { Table.Max( Table.SelectRows([All Rows], each [Status] <> ""), each [Date] ) } ), each [Record last updated] )[Status] ), #"Added Result" = Table.AddColumn( #"Added Status", "Result", each Table.Max( Table.FromRecords( { Table.Max( Table.SelectRows([All Rows], each [Result] <> ""), each [Date] ) } ), each [Record last updated] )[Result] ), #"Removed Other Columns" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"}) in #"Removed Other Columns"
Your explanation is correct, and List.Last would be same as List.First. I don't think you should use List.Max as it may grab the last one alphabetically, but I didn't test that. FYI too that, if this is slow at scale (it probably will be), you can speed things up a lot by using Table.Buffer or List.Buffer within the query to store a table/list that is referred to repeatedly (in each row).
Regards,
Pat
Thanks, I'll stick to the List.First approach then.
Could you point me to an example of this please?:
mahoneypat wrote:... you can speed things up a lot by using Table.Buffer or List.Buffer within the query to store a table/list that is referred to repeatedly (in each row).