Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kilala
Resolver I
Resolver I

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?
1 ACCEPTED SOLUTION
Zang_Mi
Resolver II
Resolver II

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.

Zang_Mi_0-1711699860640.png

 

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".

Zang_Mi_1-1711699972636.png

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

Zang_Mi_2-1711700171709.png

 

View solution in original post

1 REPLY 1
Zang_Mi
Resolver II
Resolver II

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.

Zang_Mi_0-1711699860640.png

 

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".

Zang_Mi_1-1711699972636.png

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

Zang_Mi_2-1711700171709.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.