By: Nat Sundar | Comments (1) | Related: 1 | 2 | 3 | 4 | > Power BI
Problem
I read the previous tips about developing Personal Finance calculator in Power BI tip #1 , #2 and #3. I have multiple personal accounts for savings, checking and credit cards. So how do I analyze my expenses across all my personal accounts?
Solution
It is recommended to read the previous tip #1 , #2 and #3 to develop the Personal Finance Calculator. In this tip, I will be help you to extend the model to include other personal accounts such as a credit card.
Let’s assume that we have been supplied a credit card statement in a txt file. The below mentioned image represents the format of credit card statement.
![Credit Card Format - Description: Credit Card Format](/tipimages2/5043_personal_finance_multiple_account_analysis.001.png)
It has been observed that the file is in the tab delimited format. In particular the columns are not as same the original savings transactions file mentioned in the tip #1.
So we need to create equivalent columns when we extract the credit card transactions. In addition we need to differentiate the source of expense, when we extract credit card and savings transactions. This will help us to analyze the expenses by the source.
Extracting Credit Card Transactions
Let’s extract Credit card transactions using Get Data option > Text/CSV options in the ribbon as mentioned in the image below.
![Extract Data from file - Description: Extract Data from file](/tipimages2/5043_personal_finance_multiple_account_analysis.002.png)
Power BI has identified all the columns correctly in the csv file.
![Formatting Credit card transactions - Description: Formatting Credit card transactions](/tipimages2/5043_personal_finance_multiple_account_analysis.003.png)
Let’s edit the query to enhance the dataset. It is mandatory to setup the column data types correctly in the initial step.
The below mentioned image represents the columns with the correct data types.
![Credit card changing data type - Description: Credit card changing data type](/tipimages2/5043_personal_finance_multiple_account_analysis.004.png)
The Savings file which we have loaded in tip #1 had two separate columns for debit and credit amount. However in the credit card, we have only one column (credit card/debit card). Based on the data, we can observe that the debit/credit card column has a value less than 0 if there is a debit transaction and has a value greater than 0 if there is a credit transaction. Based on this business logic, we can create separate columns for debit and credit amounts.
Now we need to use “Invoke Custom function” functionality to define the business logic to create the debit column.
![Invoke custom function - Description: Invoke custom function](/tipimages2/5043_personal_finance_multiple_account_analysis.005.png)
The below expression can be used to derive the Debit Amount column.
if [#"Debit/Credit"] < 0 then [#"Debit/Credit"]*-1 else null
The below image represents the configuration.
![Adding custom column - Description: Adding custom column](/tipimages2/5043_personal_finance_multiple_account_analysis.006.png)
![Adding debit amount column - Description: Adding debit amount column](/tipimages2/5043_personal_finance_multiple_account_analysis.007.png)
Adding Conditional Column for Credit Amount
It is easy to add a Credit Amount column, as the formula is very simple. If the debit/credit amount is greater than 0 then the column will be defined as credit amount. This can be achieved by creating a conditional column as shown below.
![Adding credit amount column - Description: Adding credit amount column](/tipimages2/5043_personal_finance_multiple_account_analysis.008.png)
Once the expression has been added, the Credit Amount column has been added to the table as per the below image.
![Adding expression for credit amount column - Description: Adding expression for credit amount column](/tipimages2/5043_personal_finance_multiple_account_analysis.009.png)
As we have derived both debit and credit columns, we don’t need to maintain the source “Debit/Credit “amount. Hence this column can be removed as in the image below.
![Removing unwanted columns - Description: Removing unwanted columns](/tipimages2/5043_personal_finance_multiple_account_analysis.010.png)
We need an additional column to differentiate the type of transaction and the value will be defined in the expression.
As we are getting the transactions from the “credit card” we can provide the value as “Credit Card”. Let’s add a custom column so that we can provide the value as below.
![Adding custom column in transaction table - Description: Adding custom column in transaction table](/tipimages2/5043_personal_finance_multiple_account_analysis.011.png)
Once the column has been added, we can see the value for the column in the data table.
![Adding new column Transaction source - Description: Adding new column Transaction source](/tipimages2/5043_personal_finance_multiple_account_analysis.012.png)
Let’s close and apply to save changes.
![Close and apply to save changes - Description: Close and apply to save changes](/tipimages2/5043_personal_finance_multiple_account_analysis.013.png)
Enhancing the Transaction Data Table
All our existing reports are based on the transaction data table. Hence we need to append the transactions data table to the credit card table. Before appending, we need to prepare the transaction table.
The transactions table has the details of all the savings transactions. Now let’s add a column to the transaction table to define the source. As shown above, the click on edit query on the transaction table to add a custom column.
Now a custom column “Transaction Source” can be added and the value can be defined as “Savings” as shown below.
![Adding transaction source column - Description: Adding transaction source column](/tipimages2/5043_personal_finance_multiple_account_analysis.014.png)
Once added, the transaction source column will be included in the table.
![Adding expression for Transaction source - Description: Adding expression for Transaction source](/tipimages2/5043_personal_finance_multiple_account_analysis.015.png)
Appending Credit Card and Savings Transactions
Now let’s append the credit card data table to the savings dataset to create a consolidated data table. This can be achieved by selecting the option “Append Queries” in the ribbon.
![Appending data tables - Description: Appending data tables](/tipimages2/5043_personal_finance_multiple_account_analysis.016.png)
On the “Append” the dialog box, select the Credit_Card data table as in the below image.
![Append transaction data table to Credit card table - Description: Append transaction data table to Credit card table](/tipimages2/5043_personal_finance_multiple_account_analysis.017.png)
Once appended, this process will create a consolidated data table which contains both the savings and credit card transactions. As we have appended the credit card data table to the Transaction table, we have a consolidated single table for reporting.
The below image confirms that both the savings and credit card transactions can be seen from the same table.
![Consolidated data table - Description: Consolidated data table](/tipimages2/5043_personal_finance_multiple_account_analysis.018.png)
Now let’s close and apply to save changes.
![Close and apply to save changes - Description: Close and apply to save changes](/tipimages2/5043_personal_finance_multiple_account_analysis.019.png)
The previously developed reports can be refreshed to see the consolidated report for both Savings and Credit card transactions.
![Conslidated reporting - Description: Conslidated reporting](/tipimages2/5043_personal_finance_multiple_account_analysis.020.png)
As we have a column “Transaction Source” to differentiate the transaction type, this can be added as a slicer for drill down reporting. Now I have created a slicer for the transaction type and selected the credit card slicer for more detailed analysis.
![Conslidated reporting with slicer - Description: Conslidated reporting with slicer](/tipimages2/5043_personal_finance_multiple_account_analysis.021.png)
Summary
In this tip, we have developed a consolidated Power BI model which accommodated multiple personal accounts such as credit card and savings transactions with different formats. This enable us to have single view on our personal expenses.
Next Steps
- Read more about the Summarize function.
- Read other Power BI Tips.
Learn more about Power BI in this 3 hour training course.
About the author
![MSSQLTips author Nat Sundar](/images/Nat-Sundar.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips