Need help with grouping and conditional column

[Jiradett]

I want to group the ID and Name and add a new column based on the status of the end date. The new column would contain the ministry names (in one cell, separate by a comma) if the end date is 'null', but display nothing if no 'null' value showing for each ID at all.

The result should be like this 

I'm not sure what technique should I use with Power Query. Please advice.

[vaibhavkale570]

1. FIlter Null values in end date table

2. select id and name column and click group by

3. in operation select All Rows and click ok

4. go to add column >custom coluumn

add this m code in formula box

=Table.Column([Ministry],"Member.ministries.ministry.name")

click ok

5. click on extract values 

6.Choose separator Comma and click ok.

remove unwanted column from table and you're done.

[Jiradett]

Thank you so much for your advice👏.

But if we filter out the null value, we'll lose Bruce (id 4) who no longer holds any position (should be blank in the Minster column).