SOLVED Extracting the Latest Comment by a Specific User from a List

Hi,

We use PowerBI to extract data from our in-house developed database system, and I am trying to create a report that will show the latest comment added against a record by a specific user and return the date and the comment text.

The data is stored in the following format:

[2022-08-17 - TP - comment text],[2022-07-01 - AB - comment text],[2022-06-06 - CD - comment text]

So, if I wanted to see when user CD last commented against a record, I would want to extract the date - 2022-06-06 - so that I can sort on date, and the associated comment for that date.

The best I have managed to achieve so far pulls the most recent date of a comment made by any user and the most recent comment by the specified user. So, if looking for user CD I get their comment but the date of 2022-08-17. This obviously works if CD was the last commenter but not otherwise.

I have tried splitting the column using ] but that is then a lot of data to look through if I am trying to identify a specific user.

Any ideas on how this could be implemented would be greatly appreciated.

Edit:

Managed to solve it with multiple use of delimiters:

1. First did a Text After Delimiter to extract the most recent comment made by the specific user
2. Then a Text Before Delimiter with the same user initials to get a column containing the comment date
3. Transformed this second column using Text After Delimiter ',' from the end of the input, to remove any more recent comments from other users. This just leaves the date but with a leading [
4. Then a Text After Delimiter '[' to just leave the date. As this is stored as text, then parsed this to create an actual date
5. The column with the specific user comment also contained all older comments, so another transform using Text Before Delimiter with ']' to leave me with the desired comment.

It's taken me the best part of three days to solve this, but got there in the end!