Identify and insert missing rows

[Anonymous]

Hi Team,

Please help if there any mechanism on identify and insert missing rows. We are working on weekly target and actuals. In actuals table we are capturing weekly actuals by resource and in target table we have pre defined weekly targets by resource. There are scenarios a particular resource not met or not filled in his/her weekly actuals at all. In that scenario we wanted to identify which is the week the particular resource misses actuals and append the relavence row from weekly targets. Below the tables for reference. Week starts from every monday. 

Weekly Targets
Resource	Week	Targets
126	9/6/2021	40
126	9/13/2021	40
126	9/20/2021	32
126	9/27/2021	40
126	10/4/2021	32
126	10/11/2021	40
126	10/18/2021	40
126	10/25/2021	40
128	9/6/2021	32
128	9/13/2021	40
128	9/20/2021	32
128	9/27/2021	40
128	10/4/2021	40
128	10/11/2021	40
128	10/18/2021	32
128	10/25/2021	24

Weekly Actuals
Resource	Week	Actuals
126	9/6/2021	10
126	9/13/2021	20
126	9/20/2021	36
126	10/11/2021	40
126	10/18/2021	31
126	10/25/2021	40
128	9/6/2021	32
128	9/13/2021	40
128	9/20/2021	32
128	9/27/2021	38
128	10/4/2021	40
128	10/11/2021	18
128	10/25/2021	24

So in Weekly Actuals table we see for Resource 126 the weeks 9/27/2021 and  10/04/2021 are missed and for Resource 128 the weeks 10/18/2021 is missing. How do I identify these missing weeks and bring in those details from Weekly Targets to Weekly Actuals table.  Expected output should like below.

Weekly Actuals (Expected Result)
Resource	Week	Actuals
126	9/6/2021	10
126	9/13/2021	20
126	9/20/2021	36
126	9/27/2021	40
126	10/4/2021	32
126	10/11/2021	40
126	10/18/2021	31
126	10/25/2021	40
128	9/6/2021	32
128	9/13/2021	40
128	9/20/2021	32
128	9/27/2021	38
128	10/4/2021	40
128	10/11/2021	18
128	10/18/2021	32
128	10/25/2021	24

Sincerely,

Krishna.

[Anonymous]

Have you checked

Solved: Inserting rows for missed days - Microsoft Power BI Community

Cheers

CheenuSing

[Anonymous]

Hi CheenuSing,

I am having difficulty downloading the link, can please share if there any steps I can follow

Sincerely,

Krishna.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can copy a new table 'Weekly Targets_copy' and try calculated columns as below:

Actuals = 
LOOKUPVALUE (
    'Weekly Actuals'[Actuals],
    'Weekly Actuals'[Resource], 'Weekly Targets_copy'[Resource],
    'Weekly Actuals'[Week], 'Weekly Targets_copy'[Week]
)

Result = 
IF (
    ISBLANK ( 'Weekly Targets_copy'[Actuals] ),
    'Weekly Targets_copy'[Targets],
    'Weekly Targets_copy'[Actuals]
)

Result will show as below:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes, I try to download the file from link shared with me, unfortunately I couldn't do it. 

Sincerely,

Krishna.