Running Ticket Reports

[jjacker]

Hi Everyone,

I'm new in Power Query and find it interesting to do the automation with reports. I'm not sure if this was asked, but I hope someone can help me with my scenario.

I created a connection in a folder. The folder will append all .xls files pasted in it (I plan to add files every week).

How can I clean the data that will only show/retain rows with the latest status of a ticket? (if it has been closed or not)

Ticket num	Open Date	Closed Date
100111​	5/1/2021​
100323​	5/5/2021​	5/19/2021​
100551​	5/5/2021​
100111​	5/1/2021​	5/5/2021​
100551​	5/5/2021​	5/15/2021​

I'm hoping to get the most efficient way to do this as the data may exceed more than a million rows since it will be a running report.

I appreciate any help you can provide.

 

[jjacker]

Hope someone could help me please.

 

[AlanSidman]

Please attach a file that is representative of your data. We cannot manipulate data in a picture.

 

[jjacker]

Thanks for your suggestion @AlanSidman

Please see attached sample data.

so basically, here's the sample data.


and here's the expected output.


Thanks

 

[p45cal]

You have unicode character 8203 (a zero-width space, where did that come from?) in all the cells with data, so those with dates aren't dates but just a string of characters, so in order to be sure of picking up the last (maximium) date, they had to be converted to true dates, recognising that they're US style dates (m/d/y).
Result at cell E1 of expectedOutput sheet of the attached.