I have to reconcile two fact tables based on One masterdata table.
We use different applications/softwares in our company for variety of work.
Each of the office has two unique IDs viz. Facility ID and Profit Center. The Facility ID of the office is permanent however the Profit Center changes if the office is attached to any other Head Office.
As we have different software/applications , in Accounts Dept. we have to reconcile the accounts data which ultimately reflects in ERP solution.
I am giving one such example of the reconciliation.
I have two sets of data each generated for the same transactions but generarated from two sources. One Fact table has a Profit Center column and the other Fact table has the Facility ID column to identify the office . Mapping of the both these dimensions is available in the Masterdata table. Due to nature of work I have to work on premise based application only.
As mentioned above, he jurisdiction of the office changes over time and due to this profit center of the office changes. The facility ID of the office remains as it is.
In the Masterdata table , Valid From and Valid To date column are present where the change in the profit centers are available. To solve this issue of the changing dimension , I have created one custom key/Surrogate key by combining Facility ID and Profit center by Concatenate function. But now I have to insert the surrogate key in the Fact Table where the Facility ID column is present. I have tried the solution on this page - https://exceed.hr/blog/merging-with-date-range-using-power-query
but it is taking too much time to load after implementing.
Please suggest any alternate solution.