Forum Discussion

ks1's avatar
ks1
Frequent Visitor
5 years ago
Solved

Grouping by three conditions

Hello,   I'd reatly appreciate some help with Power Query Grouping with data like that below, I'm trying to consildate/group to one row per ID, retaining the non-blank values in the Status and Resu...
Need Help
  • edhans's avatar
    edhans
    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"

     

ks1's avatar
ks1
Frequent Visitor
5 years ago

@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?)