none
Left Join but return only first occurrance in right table RRS feed

  • Question

  • Is there a way to set up a left join that returns only the first matching entry from the right table?

    Generic example: Join list of members to list of events in which they participated, return only first event.

    Say I have one table with a list of persons/members. My second table contains a list of event dates (0-n) on which the person participated.

    I want to

    - keep members with 0 participations in the member list

    - merge the date of the chronologically first participation for each member with 1 or more participations to the member list

    - concatenate all other participation dates into a single info text field per member ("also there on dates x, y z")

    I tried a left join, but that gives me multiple rows per member with more than one participation.

    Does anyone have an idea how to do this in Power Query?

    Wednesday, April 25, 2018 12:34 AM

Answers

All replies

  • How about you make the join on all records, then do a group on the columns you need by and select the min of date

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Wednesday, April 25, 2018 2:14 AM
  • Thanks Matt, that was a good starting point.

    Merging the two queries directly made me lose the other columns of my "members" table. But using this logic to create an intermediate table "first participation" and then left-joining the intermediate again to the original "members" table did the trick.

    Thursday, April 26, 2018 12:08 AM