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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
santu1021
Helper II
Helper II

Multiple value replace

I have huge data like below screen shot. One part is used on mutiple products (second column), each product is sparated by camma.

If one part (first column) used on mutiple products, I would like replace mutiple products with "Common Product".

I tried using Replace Values but I have different combinations of mutiple products and this will not work.

 

Any other solutions?

 

Capture9.PNG

 

2 ACCEPTED SOLUTIONS

Hi @santu1021 ,

The reason you got this error message is that you created a measure not a column. Please try to use the expression to create a calculate column instead of a measure and it will work fine.

create calculate columncreate calculate columnmeasure will have errormeasure will have error

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi,

This calculated column formula works

=if(LEN(OPC[Product Type As Is])-LEN(SUBSTITUTE(OPC[Product Type As Is],",",""))=0,OPC[Product Type As Is],"Common")

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
v-yingjl
Community Support
Community Support

Hi @santu1021 ,

If I got it correctly, you can try to create a calculate column like this:

Multiple replace =
IF (
    CONTAINSSTRINGEXACT ( 'Table'[Product], "," ),
    REPLACE ( 'Table'[Product], 1, LEN ( 'Table'[Product] ), "Common Product" ),
    'Table'[Product]
)

You will get the result like this:

multi replace.png

My sample file attached hope to help you: PBIX 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thank You Yingjie Li !!

I tried the formula but gor following error.

I have added a table from my orginal data.

Capture10.PNG

 

partproductReqruied
1EXCAVATOREXCAVATOR
8TELESCOPIC HANDLERS,VERSAHANDLERCommon
14LOADER,EXCAVATORCommon
15VERSAHANDLER,TELESCOPIC HANDLERSCommon
16LOADER,ATTACHMENTCommon
18EXCAVATOR,LOADERCommon
19EXCAVATOR,ATTACHMENTCommon
20EXCAVATOR,ARTICULATED LOADERSCommon
21ATTACHMENT,EXCAVATORCommon
22LOADER,WORK MACHINECommon
23ATTACHMENT,LOADERCommon
24ARTICULATED LOADERS,EXCAVATORCommon
25LOADER,EXCAVATOR,WORK MACHINECommon
26TELESCOPIC HANDLERS,LOADER,VERSAHANDLERCommon
27LOADER,EXCAVATOR,ATTACHMENTCommon
28EXCAVATOR,WORK MACHINECommon
29EXCAVATOR,LOADER,WORK MACHINECommon
30LOADER,TELESCOPIC HANDLERS,VERSAHANDLERCommon
31ATTACHMENT,BACKHOE LOADERCommon
32LOADER,VERSAHANDLER,TELESCOPIC HANDLERSCommon
33LOADER,EXCAVATOR,BACKHOE LOADER,WORK MACHINECommon
34EXCAVATOR,LOADER,ATTACHMENTCommon
35LOADER,ATTACHMENT,EXCAVATORCommon
36BACKHOE LOADER,ATTACHMENTCommon
37LOADER,TELESCOPIC HANDLERSCommon
38TELESCOPIC HANDLERS,VERSAHANDLER,BACKHOE LOADERCommon
39TELESCOPIC HANDLERS,ATTACHMENTCommon
40ATTACHMENT,COMPACT TRACTOR,UTILITY VEHICLECommon
41PORTABLE POWER,COMPACTIONCommon
42BACKHOE LOADER,COMPACTIONCommon
43ATTACHMENT,EXCAVATOR,LOADERCommon
44EXCAVATOR,TELESCOPIC HANDLERS,LOADER,VERSAHANDLERCommon
45ATTACHMENT,TELESCOPIC HANDLERS,LOADERCommon
46EXCAVATOR,BACKHOE LOADERCommon
47TELESCOPIC HANDLERS,EXCAVATOR,VERSAHANDLERCommon

Hi,

Share the source data in a format that can be pasted in an MS Excel file.  Also, for the dummy dataset that you share, show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

https://www.dropbox.com/sh/ixivg4q4bu2adar/AABFeyo54IWYo8tZdEOhNn0Ja?dl=0 

The file is shared with above link. I have added new column C , is what output I need.

 

If part number is used on one product, then should keep same product.

File 1.PNG

 

If part number used on different products, then should retain as "Common".

 

File 2.PNG

Hi,

This calculated column formula works

=if(LEN(OPC[Product Type As Is])-LEN(SUBSTITUTE(OPC[Product Type As Is],",",""))=0,OPC[Product Type As Is],"Common")

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @santu1021 ,

The reason you got this error message is that you created a measure not a column. Please try to use the expression to create a calculate column instead of a measure and it will work fine.

create calculate columncreate calculate columnmeasure will have errormeasure will have error

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

I'm not understanding this. What is the expected output from the provided sample data?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.