I'm trying to make a budget tracking application which reads all SMS from banks to track all money transactions.
I have a database of the following columns:
{No., Address, Name, Date, Body}
The body will contain something like this: "You card XXXX has been used for USD 0.99 at PAYPAL**ITUNES on DD-MM-YYYY AM/PM. Your available balance is USDXXX.XX"
There are many different types of text such as ATM withdraw, transfer, etc...
I've made the complete project using excel and it works 100% however I want to drop excel and only use PowerBI.
My database is automatically pulled via an online service that reads all my SMS and turns it into a database as mentioned above.
What I want to do:
Create a settings table with 5 columns
1. Banks (Contain the list of all banks that I ever received SMS from)
2. Cards (Contains all my different debit/credit card numbers as well as bank account number)
3. Debit words (Contains all specific words that indicates that this transaction was a debit, in example: "was used for", "was debited", "from your account". etc...)
4. Credit words (Contains all specific words that indicated that this transaction was a credit, in example: "was added to your", "was credited", "to your account". etc...)
5. List of different currencies with their conversions.
After creating this table then I'll create 4 specific new columns (point 2, 3 and 5 are where I'm struggling with)
1. Bank (If the address equals any of the banks in the bank list in the table mentioned above, return the bank name)
2. Card (If the text under body contains any of the cards in the cards table mentioned above, return the card number)
3. Type (If the text under body contains any words from the debit words list then return debit, if it contains any words from the credit words list then return credit, if neither return "ignore"
4. Amount (Return the amount of the transaction)
5. Amount in USD (if the currency in the text was not in USD, then convert to USD)
P.S. I've done this in excel then transferred it to PowerBI and it works amazing, however for future improvement I decided to skip excel and make powerBI do the whole calculation, can it be done this way?
