Hi Abdul, my suggestion

1. This is where Power Table comes in. Often described as "Reference Data Management" it lets you manage data which are (by design) not part of your Actuals world, like future hires
2. You create an automation, triggered e.g. by a button (currently row-based but needn´t to be limited to a row context) that starts an automation (a low-code Power Automate-like automation engine is part of the product) and checks new actuals in your new hires database. Then you can add the logic what happens if the new hire didn´t take place and to avoid double counting

Hi Abdul,

Apologies for the delay in getting back to you. We realize it has been a couple of weeks since your initial query. This is a common situation, and PowerTable handles it well. Instead of relying on the source model as the only home for dimension members, give the planning sheet its own governed master table, and keep a small mapping table that ties it back to the source.

What this setup needs

1. The planning master, the mapping table, and the list of source members need to sit in OneLake, or in the database behind your PowerTable sheets, which is where the stored procedure will run. A Direct Lake on OneLake semantic model is the setup this is built for.
2. This approach is designed for Direct Lake or DirectQuery models. If your Power BI semantic model is in Import mode, the stored procedure will still update the database, but your model won't reflect the newly mapped keys or dimension members until the next dataset refresh.

To illustrate how this works in practice: imagine a new employee is added to the planning master as P006 with no source key, and is then mapped to source key 88 once the HR source record arrives.

1. Creating a member that isn't in the source yet

Create a PowerTable sheet to act as the “planning master” for the dimension in question (e.g., customers, products, sales reps). Each row needs:

• a planning key that you control
• the source system key, left blank until the record exists
• the attributes you need for planning (Name, Department, etc.)
• a placeholder flag

Use this master, instead of the source dimension, on the rows of your planning sheet. Existing members sit in this same table with their source key already filled in, ensuring the sheet works from one consistent dimension.

When a new member needs to be planned before the source knows about it, add a row with a new planning key, leave the source key blank, and tick the placeholder flag. Assign this Planning Key to the Planning Sheet, instead of the Source Master’s key. Because the planning sheet reads from this master, the new member appears on the grid straight away and can be planned against immediately. Nothing waits on the source system.

2. Reconciling when the real member arrives

Plan values are written back and stored against the planning key, which never changes. Because of this, your plan data never has to move.

• When the member finally appears in the source system with its own key, enter that key against the member's planning key in the mapping table.
• The mapping table already holds a row for the planning key, with the source key blank. Update the Source’s SystemKey against the PlanningKey.
• Once the mapping is saved, a stored procedure updates the planning master with the source key and clears the placeholder flag. Nothing needs to be edited by hand in the planning master.
• Because actuals arrive on the source key, just relate your fact table to the planning master using that source key. The actuals will land on the exact same row that already holds your plan values, making Plan vs. Actual reporting work without re-keying anything.

This architecture avoids orphaned or double-counted values. Since the plan was never tied directly to the source key, there is nothing to re-point. The planning master remains the only dimension on the sheet, ensuring each member appears exactly once. The placeholder and the real record become the exact same row the moment they're mapped.

A few notes on this setup:

1. This approach works seamlessly across any dimension, including customers, products, and sales reps.
2. Treat the mapping as a deliberate step where someone manually confirms the planning member matches the source record. Avoid automating this based on name alone, as similar names easily mismatch.
3. Filtering the master table on the placeholder flag gives you an instant list of members still waiting for a source record.
4. For neatness, the demo keeps the mapping in its own sheet, but you can also configure this directly within a single sheet.