Calculate values based on contained strings in the data from column

[kitti]

Hi,

Please anyone advise how to calculate a value based on contained strings in the data from another column.

For instance:

Table1 has colA as text and colB as numeric; There are some data that should be calculated as percentage, so I want to * 100 if colA contains "LPercent" string value. Also, I want to add the symbol  "%"  after multiplying.

Thanks

KL

[deepak91g]

Create a calculated column

TestColumn =
IF ( ColA = "LPercent", ( ColB * 100 ) & '%', BLANK () )

[kitti]

Hi,

How do I change "=" to something like CONTAINS function? And I want to leave the original ColB data instead of BLANK (). I already tried it but got an error.

TestColumn =
IF ( ColA = "LPercent", ( ColB * 100 ) & '%', BLANK () )

Thanks

KL

[harshnathani]

 Hi @kitti ,

You can try this Calcuated Column

Column =
IF (
    CONTAINSSTRING (
        Table['ColA'],
        "LPercent"
    ),
    ( 'Table'[ColB] * 100 ) & '%',
    'Table'[ColB]
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[kitti]

I have just tried but got an error, please help

Thanks,

KL

[harshnathani · Accepted solution]

Hi @kitti ,

Try this

Column =
IF (
    CONTAINSSTRING (
        Sheet1[PCQ Item],
        "LPercent"
    ),
    ( Sheet1[Result- QTD] * 100 ) & "%",
    CONVERT (
        Sheet1[Result- QTD],
        STRING
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

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

[kitti]

@harshnathani 

Thanks a bunch. It works perfectly

Regards,

KL

[deepak91g]

You can try creating a custom column in Power Query instead of creating a DAX measure. 

https://docs.microsoft.com/en-us/powerquery-m/text-contains

Yes, you can use ColB instead of Blank() but need ensure same data type for all values