a measure formula using "contain" to capture rows including particular letters

[majdkaid22]

Hi,

a biggener question here, so bear with me please 🙂

I have a reportthat I extract manually from a (stupid) BackOffice system which have all the deposits & withdrawals transactions. 

The annoying thing, that there is no ID in the report to tag what is deposit, withdrawal, fees, rebate...etc so as seen below the Column "amount" has all the figures. 

on Excel, I came over this, by filtiering the "Comment" column to show only rows that contian (Dep or WDRL) and I remove whatever else. (pretty manual and annoying to re-do everytime, you upload a newer data)

I tried writing few Dax formulas to be kinda similar to what I want in power BI, but I failed. 

Could there be any way/formula that I can create a measure or filter rows that only contains part of the word

For instance, some of the comments will have (deposit, Dep, DEPMAS, DEPVIS) so if I sucsess by being able to filter or have a measure that can pick up "dep" I can secure that I will have the correct deposits amount. And same I can do for withdrawals. 

Thanks in advance,

Majd

[greggyb · Accepted solution]

That formula you're currently using, use exactly the same thing, but with all instances of [Comment] replaced with Text.Upper( [Comment] ), and make sure you compare to strings that are in all caps.

=if Text.Contains( Text.Upper( [Comment] ), "DEP" )
  or Text.Contains( Text.Upper( [Comment] ), "WDRL" )
then "x"
else "-"

This is what was suggested above, but I've formatted a bit nicer with line breaks.

Or, even more simply, you can use the following snippet of the above:

=Text.Contains( Text.Upper( [Comment] ), "DEP" )
  or Text.Contains( Text.Upper( [Comment] ), "WDRL" )

The return values will not be "x" and "-", but will instead be Boolean values, true and false. Just as easy to filter on in the report.

P.S. Guys, can we please make use of code blocks and share our code in text, so that it's copy-pasteable, rather than depending on people to re-write verbatim the formula you've got in a screen shot?

[Anonymous]

Hi,

Just want to say thank you @LarsSchreiber and @greggyb! Didn't realize to use Power Query in the editing window. Much appreciated.
Solved my similar issue that I had 🙂

[Anonymous]

Hi majdkaid22,

   like @LarsSchreiber asked you, the values that you are searching in "Comment" are just Dep and WDRL?

Because I have something in mind about using the SEARCH function.

[majdkaid22]

@Anonymous Hi mate,

Yes these are the values am looking for (deposits & withdrawals) however they are not the precise words "Dep" & "WDRL"

some values could be "Deposit", "Dep", "depVisa" and the criteria should search for (dep) inside the whole narrative and if found to be tagged.

Elaborate on your thoughts of SEARCH. I can see it giving a positive result 🙂

Majd  

[greggyb]

For this, Power Query is definitely the right answer like in @LarsSchreiber's post.

[LarsSchreiber]

Hello Majd,

one question to your column "Comment". Do you need it in your model only for filtering on "Dep" and "WDRL", or do you need it for more things? I am asking because as far as I can see this column has only unique values and this is very expensive in Power BI. Those columns should be avoided as possible. 

If you don't need this column for other things, I recommend you, to go to the query definition of that table and just filter the Comment column, as you can see in the screenshot below:

Therefore only click on the filter item beside the column header and use "contains". Then ust delete the column Comment. The result is a table that contains only the rows, where the comment column contained Dep or WDRL.

If you need the column Comment for more things, i would recommend to create a custom column, which identifies a specific row depending on Dep and WDRL in the Comment column. Take a look at the next screenshot:

I called that new column Filter. It returns a "x" when the Comment contains an Dep or WDRL, and a "-" if not.

Now you can create a simpler DAX statement, relying on that new custom column "Filter", which also shoul be faster, because you are scanning a column, that only has two possible values and not strings with 20 digits and more.

I hope that helps,

Regards,

Lars

[majdkaid22]

@LarsSchreiber much appreciated for the time and the great explanation. 

I did what you provided step by step and was able to get the results, however it appears that "contains" is only taking the defined text "dep" or "DEP"

below are screenshot of what I have got when I ran it over

You can see that columns 1,2,3, weren't tagged, although "DEP" is part of the text, while in other rows where DEP is a full text it has been tagged. 

And when using "Dep" a different Narrative are tagged.

i 

If we can implement in power BI something similar to text filter in Excel, where "contains" works perfectly 

And to answert your earlier question, I would only need to caputre the deposits & withdrawals (dep & wdrl)

Regards,

Majd 

[greggyb]

Power Query is case sensitive by default. First cast the entire string to upper case before checking. It is as simple as wrapping the column reference in Text.Upper() in your current formula.

[majdkaid22]

@greggyb Can you elaborate in baby steps?

am quite beginner when it comes to power query 🙂

Cheers,

Majd 

[LarsSchreiber · Accepted solution]

Hi @majdkaid22,

her comes the baby steps:

Before you do the filter operation as mentioned in my answer today, just right click the column Comments, and go to Transform and then upper case (or lower case, if you prefer that). That makes the complete column upper case. Now you can search for dep and wdrl. As @greggyb mentioned M is case sensitive, so you always have to keep that in mind, when you are searching for something.

Regards,

Lars

[majdkaid22]

@LarsSchreiber it all worked out, and I was able to create 3 different measures to represent, Gross/Net deposits and withdrawals. 

Many thanks for the details and Patience.

Cheers,

Majd

[greggyb · Accepted solution]

That formula you're currently using, use exactly the same thing, but with all instances of [Comment] replaced with Text.Upper( [Comment] ), and make sure you compare to strings that are in all caps.

=if Text.Contains( Text.Upper( [Comment] ), "DEP" )
  or Text.Contains( Text.Upper( [Comment] ), "WDRL" )
then "x"
else "-"

This is what was suggested above, but I've formatted a bit nicer with line breaks.

Or, even more simply, you can use the following snippet of the above:

=Text.Contains( Text.Upper( [Comment] ), "DEP" )
  or Text.Contains( Text.Upper( [Comment] ), "WDRL" )

The return values will not be "x" and "-", but will instead be Boolean values, true and false. Just as easy to filter on in the report.

P.S. Guys, can we please make use of code blocks and share our code in text, so that it's copy-pasteable, rather than depending on people to re-write verbatim the formula you've got in a screen shot?

[majdkaid22]

It has indeed worked. much appreciated mate

[LarsSchreiber]

Hi @greggyb,

I am absolutely with you, when you are requesting code blocks here. But when you are dealing with an absolute beginner, in my opinion it is necessarry to not only show the code, but also the GUI to show how to get there. No newby knows, which button generates which code. But next time, I will just copy the code block below in addition. Thanks for that hint.

Regards,

Lars