cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Multiple value replace

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

Highlighted
Super User IV
Super User IV

Re: Multiple value replace

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
Highlighted
Super User IV
Super User IV

Re: Multiple value replace

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Multiple value replace

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.

Highlighted
Helper II
Helper II

Re: Multiple value replace

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
Highlighted
Microsoft
Microsoft

Re: Multiple value replace

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

Highlighted
Super User IV
Super User IV

Re: Multiple value replace

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/
Highlighted
Helper II
Helper II

Re: Multiple value replace

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

Highlighted
Super User IV
Super User IV

Re: Multiple value replace

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors