account statement : if a text string contains keywords inside a table then input column title

[blackbool44]

Dear Power Bi Community,

I am new to power Bi.
Here is my issue.
I have some account statement from a personnal bank account and I want to categorize the type of spending.
1: I have created a table that I will update regularly with the Keywords that matches a specific category

Bill	Online purchase	Cash withdrawals	Groceries	Work Expense
ELECTRICITY	LAZADA	ATM	CARREFOUR	SOFITEL
JOMPAY	AMAZON	WITHDRAWAL	GROCER	RESTAURANT
WATER	SHOPPEE	CASH	TESCO	FOREIGN PURCHASE
CITY	.COM			GRAB

2: I have a list of expenses detailed description sorted by date

Date	Transaction Description	Amount RM	Transaction Category
31/03/2020	DR CD LOCAL PURCHASE GRAB -EC PETALING JAYAMY 200327	10
30/03/2020	INSTANT TRANSFER YEAST RESTAURANT SDN BHD PUBLIC BANK Yeast 3177479627	40
28/03/2020	DR CD LOCAL PURCHASE SPORTS DIRECT.COM-BANG KUALA LUMPUR	100
25/03/2020	MYDEBIT PURCHASE JAYA GROCER KL ECO 200316	200
17/03/2020	OTHER LOCAL BANK ATM CASH WITHDRAWALS 5297620952274690 11011 BUMIPUTRA-CO MY	100

I would like to be able to create a conditionnal column : if the [transaction Description] column contains a Keyword included in the Keyword table then input the column title else input "Other".

3 : This should look something similar to this :

Date	Transaction Description	Amount RM	Transaction Category
31/03/2020	DR CD LOCAL PURCHASE GRAB -EC PETALING JAYAMY 200327	10	Work Expense
30/03/2020	INSTANT TRANSFER YEAST RESTAURANT SDN BHD PUBLIC BANK Yeast 3177479627	40	Work Expense
28/03/2020	DR CD LOCAL PURCHASE SPORTS DIRECT.COM-BANG KUALA LUMPUR	100	Online Purchase
25/03/2020	MYDEBIT PURCHASE JAYA GROCER KL ECO 200316	200	Groceries
17/03/2020	OTHER LOCAL BANK ATM CASH WITHDRAWALS 5297620952274690 11011 BUMIPUTRA-CO MY	100	Cash Withdrawal

What formula should i input in my custom colum ?

Thanks a lot for your great help !

Thomas

[blackbool44]

Hello,

I have found a very interesting tutorial to do what I intended to do.
This is more merging columns than creating a new conditionnal column.

Tutorial here : https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/

Thank you so much  Justin Mannhardt for putting up such a great tutorial !

Have a great day !

[Greg_Deckler]

@blackbool44 - You know, this didn't turn out to be as nearly as ugly as I had originally thought once I unpivoted your first table and cleaned up some of the data (get rid of blanks, fix " .COM" to ".COM" for example. Once that was done, the following works (below). I attached the PBIX for reference as well. Man, the CONCATENATEX function has been coming in handy lately! 

Transaction Category = 
    VAR __Table =
        ADDCOLUMNS(
            'Table',
            "Search",FIND([Value],[Transaction Description],,-1)
        )
RETURN
    CONCATENATEX(
      DISTINCT(
        SELECTCOLUMNS(
          FILTER(__Table,[Search]<>-1)
          ,"Attribute",[Attribute]
        )
      ),
      [Attribute],", "
    )

[blackbool44]

thank you so much @Greg_Deckler .
What a nice solution ! 

I see that you have used a variable DAX Keyword....I will definitely have to learn how to use it.

I am sure a lot of people who are in the same situation will find your code very useful !!

take care !

Thomas

[blackbool44]

Hi again @Greg_Deckler ,

Just a quick question :
in your code, what is the reason you put __ (double underscore) in front of the VAR__Table ?

I am trying to include a formula at the end of your code to replace the Blank values by "Others".

I was thinking about something like this :

Transaction Category = 
    VAR __Table =
        ADDCOLUMNS(
            'Table',
            "Search",FIND([Value],[Transaction Description],,-1)
        )
RETURN
    CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Search]<>-1),"Attribute",[Attribute])),[Attribute],", ")
    IF(Len(__Table)=0,"Others",__Table)

How would you make it work ?

Thanks a lot

Thomas

[Greg_Deckler · Accepted solution]

Ah, the double underscores. Well, two reasons.

1. I think it is important to keep things that I create as variables obvious to myself. It is very easy in complex DAX calculations to start confusing yourself as to what is a column, a measure, a variable, etc. Just ensures that I don't name a variable a reserved word, etc. It's just a convention I came up with that I probably picked up from someone else or who knows. It's sort of the same reason I tend to be a stickler for capatilizing DAX functions, we all have our habits.

2. The double underscore. It's a rhythm thing. For some reason I find it more in rhythm with my typing to do a double underscore versus a single underscore. So Shift __ is just more rhythmatic for me than Shift _, it's like I find myself pausing for a second after just doing a single underscore and then trying to type a letter but with double underscore I don't have that pause. I don't know, maybe I'm weird like that. 

Let me see:

Transaction Category = 
    VAR __Table =
        ADDCOLUMNS(
            'Table',
            "Search",FIND([Value],[Transaction Description],,-1)
        )
    VAR __Category = 
    CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Search]<>-1),"Attribute",[Attribute])),[Attribute],", ")
RETURN
    IF(LEN(__Category)=0,"Others",__Category)

[blackbool44]

Thank you so much @Greg_Deckler for taking the time to share your best practice and solution !
You are really helping me to progress big time in DAX.

Cheers !

Thomas

[Greg_Deckler]

Sorry @blackbool44 yesterday was a crazy busy day. Glad you found a solution. I agree, that's a much better approach than trying to shoe horn this into a DAX calculated column.

It's an interesting problem though so I may still take a look at this when I have more time because, you never know. Often, you can learn a lot by trying to do the impossible/ridiculous.

[Greg_Deckler]

@blackbool44 That is doable but it is not exactly going to be pretty. Can you post some sample data as text for your tables? 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[blackbool44]

thank you @Greg_Deckler : I have modified the data. Let me know if this is ok.

Cheers

Thomas