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"

     

edhans's avatar
edhans
Community Champion
5 years ago

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"

 

 

ks1's avatar
ks1
Frequent Visitor
5 years ago

Hello edhans, yes in answwr to your questions, I would expect the results you have circled.  

 

For some reason, when I tried to apply mahoneypat 's code it returned only blanks in my full data set.  I'm not sure why (Iliked the simplicity too.

 

With your code, I'm just trying to work out whether the results are coming out as I expect in the full data set.  Will revert shortly.

 

Thanks boths for your help