Get data from first record in Group By

IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Get data from first record in Group By

Postby IanR » Tue Nov 21, 2017 10:42 pm

Hi,
I am grouping some web visit records. The table I am grouping contains the contact ID, the URL of the web page visited and the date and time of the visit. I would like to apply a Group By that groups by Contact ID, date (without the time) and the URL of the first page visited on that date. There are Max and Min aggregations but I suspect that if I apply these to the URL column it will just return the first URL alphabetically, not the first one visited chronologically. I want the URL from the record that has the first date/time for each contact on each date.

If I was doing this in SQL I would put a group by clause that brought back the first time for contact for the day (group on Contact ID, Date and aggregating Min(date/time) in a sub query and then join that back to the original data to get the URL. Is that kind of thing possible in Power Query?

Thanks
Ian

IanR
Posts: 13
Joined: Wed Aug 09, 2017 11:30 pm

Re: Get data from first record in Group By

Postby IanR » Wed Nov 22, 2017 1:09 am

I think the answer is here: http://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group, 'Grouping in Power Query; Getting The Last Item in Each Group'.

Looks spot on for what I was after.


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron