Equivalent of TEXTJOIN in Power Query

[clmish]

I wonder if you can suggest a solution for this business question: A given value created to be a matching lookup key between tables can have multiple records in a transaction file. I need to find the condition where the Lookup value has “conflicting” (different) values in another column between the multiple related records. I am trying to replace a TEXTJOIN formula in Excel with a query in Power Query.

Ex: Lookup Value: 1234
Record_1 Status Column: Exported
Record_2 Status: Archived
Record_3 Status: Exported
Record_4 Status: Deleted

Versus Lookup Value: 5678
Record_1 Status Column: Exported
Record_2 Status: Exported
Record_3 Status: Exported

I’m looking for certain combinations of Status values for a given record. The latter record's Action Column (new one I would add) for #5678 is “COMPLETED” and the former (#1234) would be “REVIEW”.

How can I iterate and collect these statuses per lookup value and return a table with the ones that need Review? I tried Group By, but I’m looking to just add a column with the outcome and it not listing the Status, just counting them. I have tried to Group By with Text.Combine instead of List.Sum or the typical aggregation.

I have a number of test conditions that I’m hoping to evaluate with an ACTION value, then run an “anti-join” query against an original source table to tell me which records require review. (Not “COMPLETED” Action.) There are thousands of records to evaluate, so having an efficient process would be desirable.
There’s probably a simple solution, I just don’t know enough to figure it out! Thanks, if you can suggest a direction. Deadlines!!

 

[alansidman]

Please upload a sample of your data and a mocked up solution of what you want. Use the XL2BB feature available on this site so that we don't have to recreate your data to create a solution.

 

[clmish]

I found the answer myself - Group By, use some aggregation like Sum on the text column. It shows Error, but go into the M code and change List.Sum to Text.Combine and add ", " after the column name.