Searching for specific text in a text colum

[Yumti]

Hello

In my report I'm trying to create a column in a table that would show true or false after searching for specific text in another column containing text. 

For example: A cell in column A contains the text "Ecodesign requirements – amendment of EU rules on fans driven by motors between 125 watts and 500 kilowatts". I want the new column B to return "True" if the text contains "eu".

I've tried looking at the containsstring function, which would seem to do the trick, but that only applies to measures and calculated columns. Is there any way to achieve this with another function or can you convert column A to a calculated column still containing the origial text, so I could apply the containstring function?

Thanks!

[Anonymous]

Hi @Yumti ,

Any update o this? Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Yumti ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Yumti · Accepted solution]

I gave up trying to get it to work with direct query and went back to excel.

I appreciate the help, but I'm unable to share my pbix as it also contains a lot of more sensitive data and cleaning it would be too much of a time drain.

[Anonymous]

HI @Yumti,

OK, hopes you can find some better workarounds to achieve your requirements on excel side.
Regards,

Xiaoxin Sheng

[Yumti]

Thanks for the fast reply. 

I've tried that but I can't select the specific column in the table, that I want. The formula only lets me select some measures I made previously.

[bhanu_gautam]

@Yumti , Go to modelling tab and create new calculated column and if you still face any issue share sample data

[Yumti]

I think I might know what the problem is.

I just tried downloading the data set to excel and importing in from there. Then I can add the calculated column with no issues.

The report I'm working on is getting the data from a semantic model in the OneLake-datahub and that seems to cause some issues. For one the Table View disappears entirely from my Power BI Desktop front, when importing a semantic model.

Is there any way to solve my issue when using data from a semantic model?

[bhanu_gautam]

@Yumti , Either try changing your data source or create a new measure 

Contains EU Measure =
IF(
MAXX(
FILTER(
TableName,
CONTAINSSTRING([Column A], "eu")
),
1
) = 1,
TRUE,
FALSE
)

[Yumti]

That measure can connect to the right column, but I get an expression error when trying to add it as a column to my table.

If I can't get this to work with my direct query data and have to download the data to excel before importing, then I might as well just do the whole thing in excel.

Thank you for trying though. 

[Irwan]

hello @Yumti 

are you looking like this?

[Column B] is in form of measure. IF statement in measure [Column B] will looking for "EU" string (not case sensitive) in 'Column A' then return "True" if there is a matched value.

You can see measure value in Table Visual (not in form of column like calculated column) since measure works with filter.

also if you are using Direct Query, you most likely will not see the Table View therefor you can not add calculated column in Direct Query table.

Again, you most likely will use measure in Direct Query.

Hope this will help.

Thank you.

[bhanu_gautam]

@Yumti , Create a new calcuated column like

Column B = IF(CONTAINSSTRING([Column A], "eu"), TRUE, FALSE)