PBI DAX Query: Remove duplicates and do formatting

[kilala]

Hi all, I want to add a column in PBI Query in my existing table.

Basically, I have 2 column, Name and In/Ex.

Name	In/Ex
Ali	In
Ali	Ex
Ali	Ex
Abu	In
Amat	Ex

if there is duplicate on the name, I want to check the In/Ex column. If there is In there, I want to change all to In. If not, maintain the column as it is. The expected output:

Name	In/Ex
Ali	In
Abu	In
Amat	Ex

How to do this in Power BI query?

[Zang_Mi · Accepted solution]

Hello,

You can achieve this result by doing the following:
1 - Group by data rows by Name and concatenas all the values from In/Ex.

2 - Based on the new column In/Ex, create a custom column (or just transform the current one) applying the logic: if the text contains "In" then return "In, otherwise, return "Ex".

These are the 2 steps you can add to the query: