• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running Ticket Reports

jjacker

Member
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 numOpen DateClosed 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. :)
 
Thanks for your suggestion @AlanSidman

Please see attached sample data.

so basically, here's the sample data.
74743

and here's the expected output.
74744

Thanks
 

Attachments

  • sampleData.xlsx
    9.8 KB · Views: 3
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.
 

Attachments

  • Chandoo46315sampleData.xlsx
    19.9 KB · Views: 3
Back
Top