How to Insert custom Key/Surrogate Key in Fact table

Power BI
1

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.

2

Hi @Abhijeet357, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hi @Abhijeet357, we noticed that you have not responded to our request 6 days ago. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

4

Hi @Abhijeet357, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.