Multi-source Reconciliation

[Fabian10]

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.

[Ashish_Mathur]

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.

[Fabian10]

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!

[Ashish_Mathur]

I really cannot understand your requirement.  Someone else will help you.

[Anonymous]

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