Grouping by three conditions

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  The key part is the custom #"Grouped Rows" step (after sorting by the two date columns descending).

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,{{"ID", Int64.Type}, {"Date", type date}, {"Status", type text}, {"Result", type text}, {"Record last updated", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Record last updated", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Status", each List.First(List.Select([Status], each _ <> " ")), type nullable text}, {"Result", each List.First(List.Select([Result], each _ <> " ")), type nullable text}})
in
    #"Grouped Rows"

Regards,

Pat

Hi ks1 - 

There may be a quicker way to do this. I'd have to think on it, but what I've done is repeatedly filtered the results after grouping to get the max date, then max last modified date, and ignored rows where the status or result equals a space, which is what you have in your original data. It isn't blank, or "", but " "

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}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"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"

SO 

becomes this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Both the Added Status and Added Result steps are identical in that they go back to the original Grouping. They just focus on the Status/Result columns as applicable.

Hi Many thanks for your reply.

I'm trying to fathom it by applying it to my data.  First I'm confused about the bit before the grouping 

My source is another query so I make the first lines of my query like this:

let
Source = #"q Events in report Range",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "ID", "Result", "Status", "Timestamp"}),

I think your line below your source is setting a variable of some kind and needs to come at the top, right after the source statement, but when try to do that I get an error "Token idenfifier expected" and squiggly line under the 'let' in "let _t"...

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t, Result = _t, #"Record last updated" = _t]),

ks1 - using my code or mahoneypat you need to integrate the M code. See this article and steps:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

Also ks1 I have a question.
I liked the simplicty of sorting that mahoneypat did, but I wanted to see if it would break. Please look at this table and tell me what you expect. I circled what I expected the result to be

If I redid mahoneypat's code correctly, it returns this:

Mine, which relies on Table.SelectRows() vs List.First() returns this:

What are you expecting here? 

Pat, here is your code back - I do not want to misrepresent what you've done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZI9D4IwEIb/yoWZ4e5aPsrm4uDgopthwEiABMHwMfjvrWAC5FBJ2qRp+7593rteLo5SBrXR2nEdZjj0JTDaNdh5qu9plxdVBmnZpnaDGAjtOSNQGLGJFDmxu/TAyeOcFy18vKQUB6kJA03KM4G9gxp2fbb5fYURhcKE9bcQqwYkDXieIOlWA1gpygBL6Rj+fwiUDGRgn16nEBLgPSTABp2xQ1ben3QSvG5uRZU0TwlPEUv4udlXeBwh2AsVk+8bPbZuRjFU/kf3bBL2pcm8BBt78Hby/v7GY/1YaR8N/yB+AQ==", 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,{{"ID", Int64.Type}, {"Date", type date}, {"Status", type text}, {"Result", type text}, {"Record last updated", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Record last updated", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Status", each List.First(List.Select([Status], each _ <> " ")), type nullable text}, {"Result", each List.First(List.Select([Result], each _ <> " ")), type nullable text}})
in
    #"Grouped Rows"

@edhans, for some reason I'm not getting the expected outcome with my real data.

For example in the following selection, for the ID "33904944.R1" the code brings back blanks in both result and status columns (I've replaced the confidential with "This Text" and "That Text", but the values for 22 Jul and 24 Jul are in fact the same.)

Date ID Result Status Timestamp
20-Jul-20 33904944.R1 12 Oct 20 18:29:30
20-Jul-20 33904944.R1 12 Oct 20 18:29:31
22-Jul-20 33904944.R1 12 Oct 20 18:30:00
22-Jul-20 33904944.R1 12 Oct 20 18:30:01
22-Jul-20 33904944.R1 This text That text 12 Oct 20 18:30:07
23-Jul-20 33904944.R1 12 Oct 20 18:30:11
24-Jul-20 33904944.R1 This text That text 12 Oct 20 18:30:18
4-Aug-20 33904944.R1 12 Oct 20 18:31:21
16-Sep-20 234502436.R1 12 Oct 20 18:35:46
16-Sep-20 234502436.R1 12 Oct 20 18:35:47

I adjusted one the your steps only to reflect the diffetent fieldname for the timestamp, but maybe I broke something else?  

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Timestamp", type datetime}, {"Result", type text}, {"Status", type text}}),

   #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Timestamp=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 [Timestamp]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> " "),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Result]
        ),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"})
in
    #"Removed Other Columns1"

(I'm sorry for the table format - I'm really struggling with this forum software - "Your post has been changed because invalid HTML was found in the message body" thing, when I didn't put any HTML in inthe first place - then it tells me the invalid HTML has been removed - try again.  Then it tells me "Post flooding detected" Iand I have to wait an hour?)

The reason you were seeing blank is likely because the List.Select was looking for " " which is what your example data had, but your real data probably does not.  Also, the more recent example data didn't have any status/result values for one the IDs.  I added some values and updated it to first replace blanks with null and used List.RemoveNulls instead.  You just need to make sure blank values are replaced with null prior to the custom step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQ9SrN0TUyUNJRMja2NDCxNDHRCzIE8iDI0EDf0EjfyMDIQMHQwsrIUilWh4Cm4PzcVIXiksSS0mKc+o1IsdTYgBaaQjIyixVKUitKwOzEEhgbuzHG5NhtQhW7TXQdS9OJttoQrMfQTDc4tQCiycjYxNTAyMTYDKILHD0hEPswtZsS0g5E/iUZqUU4nAzUHwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t, Result = _t, Status = _t, Timestamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", type text}, {"Result", type text}, {"Status", type text}, {"Timestamp", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Descending}, {"Timestamp", Order.Descending}}),
    #"Replaced Value" = Table.ReplaceValue(#"Sorted Rows","",null,Replacer.ReplaceValue,{"Result", "Status"}),
    Custom1 = Table.Group(#"Replaced Value", {"ID"}, {{"Status", each List.First(List.RemoveNulls([Status])), type nullable text}, {"Result", each List.First(List.RemoveNulls([Result])), type nullable text}})
in
    Custom1

Regards,

Pat

ks1 my formula is looking for a space, because that is what your original data had. Replace the two parts of the formula that look like this:

<> " ")

with this if you are getting blanks (empty) cells - no space between the quotes

<> "")

. or with this if you are getting null values in the data you do not want.

<> null)

Nulls, blanks, and spaces are all very different in Power Query. Nulls are the best to work with if you can get it because the software shows you nulls. Spaces and empty (blanks) visually look the same because spaces are invisible. You can tell something is empty vs a space if you click on it, and in the bottom window you see a cursor, but you cannot move the cursor. it just binks. If you can move it left/right, then it is spaces.

Null is again the best option because it shows "null" in the window below too.

Thanks mahoneypat , that produced the expected results.  I'm trying to understand the logic :

= Table.Group(#"Replaced Value", {"ID"}, {{"Status", each List.First(List.RemoveNulls([Status])), type nullable text}, {"Result", each List.First(List.RemoveNulls([Result])), type nullable text}})
in

I think List.First returns the first in a list and the List.RemoveNulls ignores the null/blank values, which relies on the list being sorted by date time highest first?  Presmumably List.Last would work on a list that was sorted ascending order?  But did you not suggest it because it is slower/ less efficient because it has to go to the bottom of the list?  Or doesn't it make any difference?

What I tried in the meantime was this:

Table.Group(#"Changed Type", {"ID"}, {{"Result of Referral", each List.Max([Result]), type nullable text}, {"Status", each List.Max([Status]), type nullable text}}),

It seems to produce the same results but without the list being sorted.   But is it problematic?  Would it be also less efficient? 

With your solution edhans,  modified from " " to ""  I got a Expression.Error: We cannot convert the value null to type Record. Details: Value= Type=[Type].

The same happens even if I replace the "" with null before the Added Status step:

 #"Grouped Rows" = Table.Group(#"Replaced Value", {"ID"}, {{"All Rows", each _, type table [ID=nullable text, Date=nullable date, Status=nullable text, Result=nullable text, Timestamp=nullable datetime]}}),
   #"Added Status" = 
        Table.AddColumn(
            #"Grouped Rows", 
            "Status", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Status] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Status]
        ),
    #"Added Result" = 
        Table.AddColumn(
            #"Added Status", 
            "Result", 
            each
            Table.Max( 
                Table.FromRecords(
                    {
                        Table.Max(
                        Table.SelectRows([All Rows], each [Result] <> null),
                        each [Date]
                        )
                    }
                ),
                each [Timestamp]
            )[Result]
        ),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Result",{"ID", "Status", "Result"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns1", each true)
in
    #"Filtered Rows"