Hi All,
I am new to PQ and am stumbling through.
Attached is a very simple sheet.
What I would like to do is use PQ to
a) sort by name in ascending order - easy enough
b) then sort by date in descending order - easy enough
c) return only the first occurrence ie the most recent. - not so easy :-(
thanks for your help
Jeff
Last edited by JeffGrant; 07-23-2021 at 03:18 AM.
Please try
Need to add Table.Buffer before remove duplicates
Please Login or Register to view this content.
Formula , Just for fun
=SORT(INDEX(SORT(Table1,2,-1),MATCH(UNIQUE(Table1[Name]),INDEX(SORT(Table1,2,-1),,1),),SEQUENCE(,COLUMNS(Table1))))
To get this?
Excel 2016 (Windows) 32 bit
Name Latest Date Anazone Another Gem Asghar Buskers Rush Captured In Time Moonscape My Little Miracle Plucky Dan Postman Jack Real Salty Shamus Oh Urjuwaan Watch Me Nae Nae Yee Hing Princess
Sheet: Table1
As easy as:
1. Sort on name.
2. Group on name - aggregate column on the date column set to max.
Or vice versa!
M Code:
Please Login or Register to view this content.
Last edited by AliGW; 07-23-2021 at 03:26 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks Bo Ry & Ali for such a quick response...
You folk are great
Jeff.
PS...
I am starting to think that my days of using VBA are somewhat numbered :-0
Last edited by JeffGrant; 07-23-2021 at 03:30 AM.
Glad to help.
Hi Ali,
i just did a data sort on Name in the real data set, which is some 16,000 rows deep and it returned null in every field..any idea why?
scratch that.... for some reason blank rows in the data
Last edited by JeffGrant; 07-23-2021 at 03:39 AM.
A sort will sort data, not return a null string.
Please can you explain in more detail what you did?
Perhaps provide a small sample that shows the problem?
OK - you can filter out blank rows in the query.
Hi Ali,
When I do the Group By Name, then do Max on Date, why does it return only the two columns of Name and Date?
How do I get it to return all of the other columns as well in the full data set?
You either include them in the groupings OR aggregate them. Hard to say without seeing a more realistic sample dataset.
I just attached a better data set.
Horse Name is in Column O & Meeting Date is on Column AR
Hope that helps
OK - but you haven't said which columns you want to keep.
all of them
OK - I don't think this is the most efficient way to do this!
Tell us the purpose and we can suggest other approaches.
The sole purpose is to keep the most recent record based on the most recent date the form meeting date column. Col AR.
Any other records for the same horse can be removed
All columns are required, because the source data has already been filtered via another app to get to this point.
Last edited by JeffGrant; 07-23-2021 at 05:19 AM.
Right. Let me have a think.
so I can learn something from you - why is this not efficient?
Same m-code
Same formulaPlease Login or Register to view this content.
=SORT(INDEX(SORT(Table1,2,-1),MATCH(UNIQUE(Table1[Name]),INDEX(SORT(Table1,2,-1),,1),),SEQUENCE(,COLUMNS(Table1))))
applies to Horse name and sort by start time
Last edited by Bo_Ry; 07-23-2021 at 05:34 AM.
Look at the attached. If it's what you want, I'll explain all.
looking now :-)
Bo-Ry's might be better.
Yep, that is bang on Ali..
Look at Bo-Ry's - which do you prefer?
I'll explain mine if you want mine.
Thanks Bo Ry, but i have absolutely no idea what this is doing mate. Sorry
I had a look at the spread sheet Bo Ry created. I am not sure what he is doing, but the data is not in the format I require. Yours is.
Most recent record on one line -> one line is what gets pick up at the next stage
Does the ALTERNATIVE tab provide you with what you want? Please look and let me know.
I have just compared everything, I may need new glass because i cant the difference between Filtered Table and Alternative :-(
Right. So the ALTERNATIVE is what Bo-Ry was trying to show you. There are three steps after loading your source query into PQ:
1. Sort descending on form meeting day.
2. Right click at the top of the horse name and select Remove Duplicates.
3. Sort alphabetically on horse name.
That's it.
Last edited by AliGW; 07-23-2021 at 06:18 AM.
ok thanks. I'll play with it now
a) sort by horse name in ascending order - easy enough
b) then sort by start time in descending order - easy enough
c) return only the first occurrence of horse name ie the most recentPlease Login or Register to view this content.
Please Login or Register to view this content.
44389.53472 => 12-Jul-21 12:50
really?but the data is not in the format I require
Nope. I'm Sorry. I don't get it.
The steps you described above do not give the results that in the Alternative table.
Almost Always, the form meeting date is not a duplicate date.
Whom are you addressing now? Me or Bo_Ry?
Sorry Bo Ry, I have very little experience with PQ. It is completely foreign to me
you mate, I didn't realise Bo Ry was still in the conversation until just now :-)
My instruction at step 2 was incorrect - sorry. You remove duplicates on the horse name column (I've updated my post).
GOT IT ! - YIPPEEEEEEEE
i stupidly removed duplicates based up on meeting date...not horse name...
the words - I am an idiot - come to mind
Just read your post......worked it out at the same time..
Both of your are worth way more money :-)
I spent hours working on this with VBA today....looks like i'll spend more time in PQ
anyway, thanks again....
the Olympics Opening Ceremony is about to start
More money than diddly squat is not much.
But you can reward us if you wish: you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Did that before...
The forum wouldn't let me give you an "Add Reputation" said, I had to share it around a bit....
Give some to Bo_Ry.
Already did :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks