"Group by" based on date and status

[ccindyp]

Hello, 

I use a dataset of tickets reported on different dates. When a ticket is updated, there is a new row with the same ID ticket, but a different status (so I get duplicates every time I have an update on the ticket). The update frequency is once a week. I needed to execute a series of calculations to take only the latest change on the ticket. 

In the power query editor, I did a "group by" and so far it was working. 

 Now I am facing that the same day, the ticket is changed from open to closed (or any other status). Example:

My ticket is then counted twice, because I filtered only on the max date of the "changed on" column. 

I need to add and aggregation so that my ticket row appears only one in this table. 

From this table I have all my calculations (open, closed, filters, last N days updated, events in progress) and relationships built. 

I have a dim table with the "Status" where I can prioritize:

1-Closed

2-In process, etc

but first the system needs to check the changed date.

I am not able to mirror other tickets solutions:

https://community.fabric.microsoft.com/t5/Desktop/Rows-aggregation-based-on-various-criteria/m-p/307...

https://community.fabric.microsoft.com/t5/Desktop/Group-by-based-on-specific-values-criteria/m-p/585...

https://community.fabric.microsoft.com/t5/Desktop/Group-by-Filter-on-agregate-level-based-on-row-lev...

Do you have any idea how I can achieve this aggregation?

Thank you very much.

[ccindyp]

Hello and Thank you for your quick reply.

It seems to be working!

I am not sure I understand the code :

#"Grouped Rows" = Table.FromRecords(Table.Group(#"Changed Type", {"Ticket"}, {{"All", each Table.Max(_,"Last updated date")}})[All])

Could you please explain what you modified?

It would be helpful for me to debugg if I need to change something else in the future. 

Thank you

[Vijay_A_Verma]

This is a little bit advanced thing which you will become comfortable as you move along in your PQ journey. I will explain here without overloading you. 

Table.Group(#"Changed Type", {"Ticket"}, {{"All", each Table.Max(_,"Last updated date")}})

This part groups the table on ticket and extracts the row (record) with maximum on Last updated date.

When I put [All] at the end of this, it extract the All column of Grouped records otherwise table will have one column Ticket and one All. All column contains grouped tables and those grouped tables are already having Ticket column. So, Ticket column was not required again.

Now we have a list where records are there (A record is a one row table in simplistic manner)

Table.FromRecords will prepare the table from this list of records.

Now, the same can be done in many steps as well like group the table, remove the Ticket column and all other operations. The more you practice the more you will be confident of doing advanced transformations. 

[Vijay_A_Verma · Accepted solution]

Table.Max on Last updated date will work in your case.

You can use this code by pasting it in Advanced Editor.
Replace Source line with your Source line after testing.
Your Source line will be something like 
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJR8i9IzQNSxvqG5vpGBkYmSrE6MKnQgpTEktQUHLLOOfnFMEkLdMmg1Hy4uUiSRkZGCCuN9A2NEFLGxsbIUkYW6FJw+5AkYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ticket = _t, Status = _t, #"Last updated date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket", Int64.Type}, {"Status", type text}, {"Last updated date", type date}}),
    #"Grouped Rows" = Table.FromRecords(Table.Group(#"Changed Type", {"Ticket"}, {{"All", each Table.Max(_,"Last updated date")}})[All])
in
    #"Grouped Rows"