Power Query Custom Column to determine final status of grouped rows with different status'

[jjfr]

Hi All,

I'm trying to clean some problematic data in Power Query. The data shows bookings for events, and is meant to give a final attendance status (e.g. cancelled, attended, booked...). However the data export (that we unfortunately can't change) is pulling a row for every attendance status change, rather than just giving us a single row per attendee with their final attendance status. It also does not give us a Status Change Date so I can't filter for the latest status.

Do you have any suggestions on how to write a nested if/switch statement that groups all matching rows (by Event ID and Attendee), and then determines a final status for that group based on criteria that I can write into M (not by a backend hierarchy). For example:

if any Attendance Status = Attended then Final Status = Attended else

if any Attendance Status = Cancelled then Final Status = Cancelled etc.

Below is an example of how I would like the Final Status to calculate:

And here is the ideal output for my query:

Any help you can give will be greatly appreciated. Thanks in advance!

[ryan_mayu]

@jjfr 

pls see if this is what you want

Column =

var _status=maxx(FILTER('Table','Table'[Event ID]=EARLIER('Table'[Event ID])&&'Table'[Attendee]=EARLIER('Table'[Attendee])&&'Table'[Attendance Status]="Attended"),'Table'[Attendee])

return if(_status="","Cancelled","Attended")

[jjfr]

Thanks so much for the suggestion @ryan_mayu. It's not working for me in Power Query, possibly because I'd incorrectly mentioned DAX not M as the code? When I try it I'm getting a 'Token EoF expected' error at the point where the variable is defined on the first line. Sorry if this is basic user error, I'm still pretty new to PBI.

[ryan_mayu · Accepted solution]

@jjfr 

if you want to do this in PQ

you can try this

= Table.Group( Source,{"Event ID ", "Attendee"}, {"col", each if List.Contains([Attendance Status],"Attended") then "Attended" else "Cancelled"})

pls see the attachment below