Fct to Act different granularities modelling

Power BI Data Model
1

Hi there,

I’m attempting to merge as new query HC_FTE actual data with its HC_FTE forecast.

The goal is to keep in one single table, named HC_Consolidated, both attributes Act and Fct. For Fct values I would normally expect to have them until year-end Dec’22, whereas Act would only reach up to latest closing period Jan’22, for the rest I would expect to see null.

I used as matching KEY between tables Employee Number, considering HC_Forecast the first table (it contains more attribute columns I want to retain) matching it with HC_Actuals. As showcased below I made it Left Outer:

image
image733×668 27.4 KB

Something I don’t quite understand is: how is it possible that the table I’m getting is again HC_Forecast if there linkage is between HC_Forecast & HC_Actuals? See below:

image
image1890×290 37.2 KB

At the end of the day, the idea behind this need is rather basic and should look like this - only including much more attributes from both tables:

image
HC Act to Fct.xlsx (268.5 KB)

I attach here a sample in case anyone has faced this sort of strange situation.

Thanks a lot

2

Hi @SamSPAIN - What I understand is you are checking why the output is in same table. Reason is in Power Query there are two option i.e. “Merge Queries” and “Merge Queries as New”.

First one will provide the merge output in the same table while “Merge Queries as New” will create a separate query.

Merge

If issue is different, then please clarify again and I will be happy to help.

Thanks
Ankit J

3

Thanks @ankit - found out what the root cause was… simply because I merge them as new ONLY taking Employee Number alone without Date. By having 2 linkages between tables that solved the issue.

4

Hi @SamSPAIN - Good to know that the issue is resolved. Please mark the post as Solution.

Thanks
Ankit J