Forum Discussion
Helper Iaccount statement : if a text string contains keywords inside a table then input column title
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
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)
9 Replies
- 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
Community Champion
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
- Greg_DecklerSorry 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
