PowerQuery - Filter to only earliest record

[DaveBlakeMAAT]

Hi

I have a small PowerQuery issue which I am hoping one of you fine ladies or gents can help with

My data is broadly as follows:-

ID JobNo DateOnSite TimeOnSite
1 1234 01/01/2017 00:53
2 1234 02/01/2017 09:47
3 4567 02/02/2018 08:56
4 4567 03/02/2018 10:56

I need to be able to filter the records so that I show the earliest date (initial onsite time) for each job number (lowest ID number for each job number will always be the initial onsite date).

To give some context, I need to calculate initial response time/date for compliance reporting and therefore only interested in the initial visit and this query will then be merged into another larger query which I know how to do, it is just this initial filtering that I am stuck on, so any pointers in the right direction will be greatly appreciated.

Many thanks

Dave

 

[Guenther45]

Hi Dave,
I'll try to help you or maybe give you an appropriate hint. Since English is not my native language, it may be that I did not understand you correctly ...


In order to filter only the youngest entry from each JobNo and output it in a new query, I do this in Power Query:
* Load data into the editor
* TimeOnSite may be set as a type of time
* Custom DateTime column using this formula: Number.From ([DateOnSite]) + Number.From ([TimeOnSite])
* Sort JobNo in ascending order
* Sort DateTime in ascending order
* JobNo -> remove duplicates
* Delete column DateTime


Since my M code contains many German names, I leave it in this description.

Best regards
Guenther

 

[DaveBlakeMAAT]

Thanks Guenther

That is almost perfect for what I need, I have a small issue in the fact that job numbers are negative if not confirmed but I can get around that using a RIGHT function and sorting from that. I think as normal I was overthinking the problem!

Your help has been very much appreciated.

Regards

Dave

 

[Guenther45]

Hi Dave,
thanks for the feedback.
You can also try using the Number.Abs function in a new help column for sorting. Number.Abs([ProductNumber]) will always return a positive value.

Regards
Guenther

 

[SOQLee]

Hi Dave,

If you're still looking for a solution...I had posted similar scenario and ParamRay replied with the code to an excellent solution for me.

Check out: https://www.mrexcel.com/forum/power-bi/1046897-power-query-2016-return-latest-date-subset.html