Value checking - Multiple reasons in same cell (for errors/duplicates)

[Ceha]

Hello,

I need help with Power Query.

I have 2 excel files.

First with a list of skills and Second with list of users.

These users come with a specific skills which I need to compare according to the First file. If the skill is not on the list from First file, then return an error. Also I need to watch out for duplicates.

My current validation is working fine but its not "all in one" but rather one at a time. If a duplicate is found, then flag it as a duplicate, if wrong skill is found, flag it as a wrong skill. Problem was when the record was duplicated and with wrong skill, it FIRST validated it as a duplicate and didnt validate for error skill.

For now I modified the query so it check both for duplicate and error skill, but I want it to come in better looking table format. Please check tables below for better understanding. I had a problem how to phrase it in google so I am came here 🙂

Table1 is the initial table that is coming to me.

Email	Name	Country	skill	working from	comment
user1@email	Adam	England	java	10.10.2023
user2@email	Peter	Germany	c#	15.09.2023
user3@email	Tom	France	errorskill	20.10.2023
user1@email	Adam	England	java	10.10.2023
user4@email	Anna	USA	manager	01.11.2023
user5@email	Jackie	Belgium	HR	15.12.2023
user3@email	Tom	France	errorskill	20.10.2023

Table2 is the table is what I currently have.

Email	Name	Country	skill	working from	comment	Rejection reason
user1@email	Adam	England	java	10.10.2023		Duplicate
user1@email	Adam	England	java	10.10.2023		Duplicate
user3@email	Tom	France	errorskill	20.10.2023		Duplicate
user3@email	Tom	France	errorskill2	20.10.2023		Duplicate
user3@email	Tom	France	errorskill	20.10.2023		Unknown skill
user3@email	Tom	France	errorskill2	20.10.2023		Unknown skill

Table3 is the table that I would like to get.

Email	Name	Country	skill	working from	comment	Rejection reason
user1@email	Adam	England	java	10.10.2023		Duplicate
user1@email	Adam	England	java	10.10.2023		Duplicate
user3@email	Tom	France	errorskill	20.10.2023		Duplicate, Unknown skill
user3@email	Tom	France	errorskill2	20.10.2023		Duplicate, Unknown skill

or maybe this (depending what will be easier)

Email	Name	Country	skill	working from	comment	Rejection reason
user1@email	Adam	England	java	10.10.2023		Duplicate
user3@email	Tom	France	errorskill	20.10.2023		Duplicate, Unknown skill
user3@email	Tom	France	errorskill2	20.10.2023		Duplicate, Unknown skill

Can anyone can give some tips how to achieve this? 

[slorin · Accepted solution]

Hi

Group and use Text.Combine

Table.Group(
  Table2,
  {"Email", "Name", "Country", "skill", "working from", "comment"},
  {{"Rejection reason", each Text.Combine(List.Distinct([Rejection reason]),", "), type text}}
  )

 Stéphane

[Ceha]

Thank you Stéphane!

Worked like a charm. I knew it will be some sort of "easy" solution. 

Thanks!