How to Insert Missing Week Numbers

[JeevanMallya]

I have a Table with Incoming Customer Defects.In some Weeks there are no customer defects reported. So i want to add Missing WEEK_NUM of that year and against those Missing Week_Num, Count as Zero.

Here in the table you will see that Week Num 54, 1, 5, 8... missing.

Can you please help me how to insert those in Power BI.

PROJECT_KEY	YEAR	WEEK_NUM	Count
ABC	2022	45	1
ABC	2022	46	3
ABC	2022	47	5
ABC	2022	48	11
ABC	2022	49	14
ABC	2022	50	4
ABC	2022	51	8
ABC	2022	52	5
ABC	2022	53	1
ABC	2023	2	2
ABC	2023	3	4
ABC	2023	4	5
ABC	2023	6	2
ABC	2023	7	4
ABC	2023	8	2
ABC	2023	9	1
ABC	2023	10	4
ABC	2023	11	1
ABC	2023	12	5
ABC	2023	13	1
ABC	2023	14	4
ABC	2023	15	2
ABC	2023	16	7
ABC	2023	17	7
ABC	2023	19	6
ABC	2023	20	3
ABC	2023	21	4
ABC	2023	22	3
ABC	2023	23	1
ABC	2023	24	1
ABC	2023	25	4
ABC	2023	26	1
ABC	2023	29	2
ABC	2023	32	1
ABC	2023	33	3
ABC	2023	34	1
ABC	2023	37	1
ABC	2023	39	6
ABC	2023	40	6
ABC	2023	42	1
ABC	2023	44	1
ABC	2023	46	1
ABC	2023	47	3
ABC	2023	48	1
ABC	2023	49	2
ABC	2023	50	3
ABC	2023	51	2
ABC	2023	52	1

[NaveenGandhi]

Hello @JeevanMallya 

The attached file has the solution that you need. Let me know if this works for you.

If this post helps, then please consider Accepting it as the solution to help the others find it more quickly. Appreciate you kudos!!

[JeevanMallya]

Hi @NaveenGandhi  : Thank you help.

Can you please help me in understanding how to build this.I see from the attached file that a new table Week_Num is created that generates series from 1 to 54 week Numbers and a Measure Count is created for inserting Zeros.

I do see that Value and Count- gives the requirement. But how do i merge this in single table (Data table in this case) since i want this in single table that will help me later visually build and display Data for all weeks. LIke

PROJECT_KEY	YEAR	WEEK_NUM	Count
ABC	2022	45	1
ABC	2022	46	3
ABC	2022	47	5
ABC	2022	48	11
ABC	2022	49	14
ABC	2022	50	4
ABC	2022	51	8
ABC	2022	52	5
ABC	2022	53	1
ABC	2022	54	0
ABC	2023	1	0
ABC	2023	2	2
ABC	2023	3	4
ABC	2023	4	5
ABC	2023	5	0
ABC	2023	6	2
ABC	2023	7	4
ABC	2023	8	2
ABC	2023	9	1
ABC	2023	10	4
ABC	2023	11	1
ABC	2023	12	5
ABC	2023	13	1
ABC	2023	14	4
ABC	2023	15	2
ABC	2023	16	7
ABC	2023	17	7
ABC	2023	19	6
ABC	2023	20	3
ABC	2023	21	4
ABC	2023	22	3
ABC	2023	23	1
ABC	2023	24	1
ABC	2023	25	4
ABC	2023	26	1
ABC	2023	29	2

Can you please help.

[NaveenGandhi · Accepted solution]

Hello @JeevanMallya 

Just create a relationship based on yourtable[weeknum] and the Weeknum table created using generate series and use weeknum from the custom table created (Weeknum[value] from below SS).

If this post helps, then please consider Accepting it as the solution to help the others find it more quickly. Appreciate you kudos!!