Multiple value replace

[santu1021]

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?

[v-yingjl · Accepted solution]

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

[v-yingjl]

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:

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.

[santu1021]

Thank You Yingjie Li !!

I tried the formula but gor following error.

I have added a table from my orginal data.

part	product	Reqruied
1	EXCAVATOR	EXCAVATOR
8	TELESCOPIC HANDLERS,VERSAHANDLER	Common
14	LOADER,EXCAVATOR	Common
15	VERSAHANDLER,TELESCOPIC HANDLERS	Common
16	LOADER,ATTACHMENT	Common
18	EXCAVATOR,LOADER	Common
19	EXCAVATOR,ATTACHMENT	Common
20	EXCAVATOR,ARTICULATED LOADERS	Common
21	ATTACHMENT,EXCAVATOR	Common
22	LOADER,WORK MACHINE	Common
23	ATTACHMENT,LOADER	Common
24	ARTICULATED LOADERS,EXCAVATOR	Common
25	LOADER,EXCAVATOR,WORK MACHINE	Common
26	TELESCOPIC HANDLERS,LOADER,VERSAHANDLER	Common
27	LOADER,EXCAVATOR,ATTACHMENT	Common
28	EXCAVATOR,WORK MACHINE	Common
29	EXCAVATOR,LOADER,WORK MACHINE	Common
30	LOADER,TELESCOPIC HANDLERS,VERSAHANDLER	Common
31	ATTACHMENT,BACKHOE LOADER	Common
32	LOADER,VERSAHANDLER,TELESCOPIC HANDLERS	Common
33	LOADER,EXCAVATOR,BACKHOE LOADER,WORK MACHINE	Common
34	EXCAVATOR,LOADER,ATTACHMENT	Common
35	LOADER,ATTACHMENT,EXCAVATOR	Common
36	BACKHOE LOADER,ATTACHMENT	Common
37	LOADER,TELESCOPIC HANDLERS	Common
38	TELESCOPIC HANDLERS,VERSAHANDLER,BACKHOE LOADER	Common
39	TELESCOPIC HANDLERS,ATTACHMENT	Common
40	ATTACHMENT,COMPACT TRACTOR,UTILITY VEHICLE	Common
41	PORTABLE POWER,COMPACTION	Common
42	BACKHOE LOADER,COMPACTION	Common
43	ATTACHMENT,EXCAVATOR,LOADER	Common
44	EXCAVATOR,TELESCOPIC HANDLERS,LOADER,VERSAHANDLER	Common
45	ATTACHMENT,TELESCOPIC HANDLERS,LOADER	Common
46	EXCAVATOR,BACKHOE LOADER	Common
47	TELESCOPIC HANDLERS,EXCAVATOR,VERSAHANDLER	Common

[Ashish_Mathur]

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.

[santu1021]

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.

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

[Ashish_Mathur · Accepted solution]

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.

[v-yingjl · Accepted solution]

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 columnmeasure 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]

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