Forum Discussion
Check two base with sequence data
I do some payment checks with Power BI made from a bank reconciliation. The problem is that when doing the reconciliation, the bank statement comes out with an amount referring to a group of checks, while my checking is done in a unitary way. Below I demonstrate how my structure is and what I try to achieve.
Below I have my report that I extract from the bank and add two columns that are in red on the right, so you can check it out manually:
| BANK | DATE | DESCRIPTION | DOCUMENT | VALUE | DATE CONFERENCE | SEQUENCE |
| 2 | 30/07/21 | DEPOSIT MONEY | 123656 | 500,00 | 30/07/21 | 10 |
| 2 | 30/07/21 | DEPOSIT CHEC | 5435345 | 30000,00 | 30/07/21 | 11-14 |
The spreadsheet below is a report that I extract from a system:
| BANK | DATE | DESCRIPTION | SEQUENCE | VALUE |
| 2 | 30/07/21 | MONEY 998 | 10 | 500.00 |
| 2 | 30/07/21 | PREDATED CHECK 2391 | 11 | 5,000.00 |
| 2 | 30/07/21 | PREDATED CHECK 9873 | 12 | 10,000.00 |
| 2 | 30/07/21 | PREDATED CHECK 988 | 13 | 5,000.00 |
| 2 | 30/07/21 | PREDATED CHECK 9883 | 14 | 10,000.00 |
I merged the SEQUENCE and DATE columns of the two tables to find out which payments were made, but I wanted to automate the checks, as it is done automatically by the system I generate.
The image below is what I demonstrate with all data manipulation. Which is to check if the company's accounts were consolidated by the bank statement.
Is there any way for me to write, for example, in the sequence column "11-14" and PowerBI recognizes that I'm referring to payments in that range?
3 Replies
renatopnovaes Use a numeric range slicer based on SEQUENCE column? Sorry, I'm not certain I understand the ask.
my idea is, put the number range in the spreadsheet extracted from the bank, and powerbi recognize that that range must be recognized that the payment has already been written off. Because, in the bank statement, the checks are grouped together. However, in the account report that I extract the checks, they are shown in a unitary way.
I do not know if i was clear
Hi renatopnovaes ,
I don't understand your needs too much. This is a solution that I partially understood, you can refer to it.
I expanded the SEQUENCE column to get the following. For specific tutorials, please refer to this article or view my attachment.
This way you can use the Custom.1 column to establish a relationship with the SEQUENCE column of your other table.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
