Forum Discussion
Multi-source Reconciliation
Hello everyone,
I'm relatively new to working with Power BI and I've encountered a challenge for which I am seeking a suitable approach. I aim to perform reconciliation processes for data coming from approximately 50 different sources. So far, most solutions I've come across only address reconciling between two tables, but I need to extend this process to a much larger set of tables.
The data across all tables have the same structure with the following columns: "Reported By," "Document Type," "Document Number," "Document Date," and "Towards Partner."
I would like to create two types of reports in Power BI:
1. A report that lists all items where the reconciliation between liabilities and receivables shows a difference of zero.
2. A report that reveals the deltas between two partners.
How could I efficiently manage this type of complex reconciliation in Power BI? Are there any best practices or tools within Power BI that can facilitate this kind of task?
I appreciate any advice or insights you can provide!
Best regards.
4 Replies
HI Fabian10,
Perhaps you can try to use the lakehouse to collection and getting data from the different type of data sources, then you only need to use power bi to getting data from data lake.
Lakehouse end-to-end scenario: overview and architecture - Microsoft Fabric | Microsoft Learn
Options to get data into the Lakehouse - Microsoft Fabric | Microsoft Learn
Regards,
Xiaoxin Sheng
Hi,
To start with, share 3 tables and for those tables, show the expected result. Share the 3 tables in a format that can be pasted in an MS Excel file.
Hi, thank you for your quick response. Since I unfortunately cannot drop Excel files here, I will use the available table function.
Table 1 is from the perspective of organizational unit A:
Reported by Document type Document Number Document Date Amount Towards Partner A Receivable 11 01.01.2024 5.000 X A Receivable 55 01.01.2024 10.000 X A Payable 999 01.01.2024 50.000 X Table 2 is from the perspective of organizational unit B:
Reported by Document type Document Number Document Date Amount Towards Partner B Payable 44 01.01.2024 30.000 A B Payable 90 01.01.2024 12.000 X B Receivable 80 01.01.2024 12.000 X Table 3 is from the perspective of organizational unit X:
Reported by Document type Document Number Document Date Amount Towards Partner X Payable 11 01.01.2024 5.000 A X Payable 55 01.01.2024 10.000
A X Receivable 999 01.01.2024 50.000 A X Receivable 12 01.01.2024 20.000 B That's the starting point. The result should be divided into 2 tables, Table "Consistent" and Table "Inconsistent".
Tabelle Consistent:
Voting partner 1 Voting partner 2 Conclusion A X no deviation
Tabelle Inconsistent:
Voting partner 1 Voting partner 2 Document Number Magnitude of deviation A B 44 30.000 B X 90 12.000 B X 80 12.000 B X 12 20.000 Ideally, a filter card should be created for the last two tables, in which one can select both voting partners and then see all transactions between them.
Thank you in advance!
I really cannot understand your requirement. Someone else will help you.
