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

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"