Forum Discussion

kilala's avatar
kilala
Resolver I
2 years ago
Solved

PBI DAX Query: Remove duplicates and do formatting

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

 

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

NameIn/Ex
AliIn
AliEx
AliEx
AbuIn
AmatEx

 

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:
NameIn/Ex
AliIn
AbuIn
AmatEx

How to do this in Power BI query?
Show and Tell
  • Zang_Mi's avatar
    Zang_Mi
    2 years ago

    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:

     

1 Reply

  • Zang_Mi's avatar
    Zang_Mi
    Resolver II
    2 years ago

    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: