Need to Insert Weeks Numbers for missing Rows for corresponding years

[JeevanMallya]

I am trying to do some calculation with Incoming Customer bugs. As you will notice in the Table there are some weeks where no customer bugs are reported.So in such cases in want to insert Week Numbers in WEEK_NUM column and corresponding CREATED_COUNT Column with Zero (0) and ISSUE_KEY column will be empty. Here is the table

PROJECT_KEY	YEAR	WEEK_NUM	ISSUE_KEY	CREATED_COUNT
ABC	2022	45	ABC-621	1
ABC	2022	46	ABC-624,ABC-630,ABC-631	3
ABC	2022	47	ABC-646,ABC-660,ABC-674,ABC-675,ABC-676	5
ABC	2022	48	ABC-677,ABC-678,ABC-679,ABC-680,ABC-681,ABC-682,ABC-683,ABC-684,ABC-687,ABC-688,ABC-691	11
ABC	2022	49	ABC-699,ABC-700,ABC-701,ABC-702,ABC-703,ABC-704,ABC-705,ABC-706,ABC-711,ABC-712,ABC-713,ABC-731,ABC-735,ABC-736	14
ABC	2022	50	ABC-754,ABC-802,ABC-803,ABC-811	4
ABC	2022	51	ABC-835,ABC-839,ABC-843,ABC-844,ABC-845,ABC-846,ABC-848,ABC-849	8
ABC	2022	52	ABC-850,ABC-856,ABC-863,ABC-865,ABC-866	5
ABC	2022	53	ABC-879	1
ABC	2023	2	ABC-898,ABC-899	2
ABC	2023	3	ABC-909,ABC-911,ABC-916,ABC-917	4
ABC	2023	4	ABC-939,ABC-941,ABC-942,ABC-970,ABC-971	5
ABC	2023	6	ABC-987,ABC-989	2
ABC	2023	7	ABC-996,ABC-1001,ABC-1002,ABC-1008	4
ABC	2023	8	ABC-1025,ABC-1026	2
ABC	2023	9	ABC-1049	1
ABC	2023	10	ABC-1091,ABC-1100,ABC-1108,ABC-1109	4
ABC	2023	11	ABC-1161	1
ABC	2023	12	ABC-1166,ABC-1167,ABC-1168,ABC-1181,ABC-1188	5
ABC	2023	13	ABC-1196	1
ABC	2023	14	ABC-1223,ABC-1224,ABC-1229,ABC-1230	4
ABC	2023	15	ABC-1271,ABC-1276	2
ABC	2023	16	ABC-1290,ABC-1291,ABC-1304,ABC-1306,ABC-1307,ABC-1308,ABC-1309	7
ABC	2023	17	ABC-1311,ABC-1327,ABC-1328,ABC-1330,ABC-1331,ABC-1332,ABC-1345	7
ABC	2023	19	ABC-1374,ABC-1375,ABC-1376,ABC-1382,ABC-1420,ABC-1421	6
ABC	2023	20	ABC-1434,ABC-1463,ABC-1464	3
ABC	2023	21	ABC-1474,ABC-1478,ABC-1483,ABC-1485	4
ABC	2023	22	ABC-1497,ABC-1504,ABC-1512	3
ABC	2023	23	ABC-1525	1
ABC	2023	24	ABC-1540	1
ABC	2023	25	ABC-1566,ABC-1581,ABC-1582,ABC-1594	4
ABC	2023	26	ABC-1619	1
ABC	2023	29	ABC-1734,ABC-1741	2
ABC	2023	32	ABC-1801	1
ABC	2023	33	ABC-1839,ABC-1843,ABC-1845	3
ABC	2023	34	ABC-1846	1
ABC	2023	37	ABC-1915	1
ABC	2023	39	ABC-1942,ABC-1943,ABC-1944,ABC-1946,ABC-1955,ABC-1956	6
ABC	2023	40	ABC-1963,ABC-1968,ABC-1969,ABC-1973,ABC-1974,ABC-1979	6
ABC	2023	42	ABC-2010	1
ABC	2023	44	ABC-2055	1
ABC	2023	46	ABC-2100	1
ABC	2023	47	ABC-2152,ABC-2153,ABC-2155	3
ABC	2023	48	ABC-2170	1
ABC	2023	49	ABC-2181,ABC-2184	2
ABC	2023	50	ABC-2187,ABC-2188,ABC-2199	3
ABC	2023	51	ABC-2227,ABC-2232	2
ABC	2023	52	ABC-2239	1
ABC	2024	4	ABC-2308,ABC-2333	2
ABC	2024	5	ABC-2341	1
ABC	2024	7	ABC-2387	1

Table needed

PROJECT_KEY	YEAR	WEEK_NUM	ISSUE_KEY	CREATED_COUNT
ABC	2022	1		0
ABC	2022	2		0
ABC	2022	3		0
ABC	2022	4		0
ABC	2022	5		0
ABC	2022	6		0
ABC	2022	7		0
ABC	2022	8		0
ABC	2022	9		0
ABC	2022	10		0
ABC	2022	11		0
ABC	2022	12		0
ABC	2022	13		0
ABC	2022	14		0
ABC	2022	15		0
ABC	2022	16		0
ABC	2022	17		0
ABC	2022	18		0
ABC	2022	19		0
ABC	2022	20		0
ABC	2022	21		0
ABC	2022	22		0
ABC	2022	23		0
ABC	2022	24		0
ABC	2022	25		0
ABC	2022	26		0
ABC	2022	27		0
ABC	2022	28		0
ABC	2022	29		0
ABC	2022	30		0
ABC	2022	31		0
ABC	2022	32		0
ABC	2022	33		0
ABC	2022	34		0
ABC	2022	35		0
ABC	2022	36		0
ABC	2022	37		0
ABC	2022	38		0
ABC	2022	39		0
ABC	2022	40		0
ABC	2022	41		0
ABC	2022	42		0
ABC	2022	43		0
ABC	2022	44		0
ABC	2022	45	ABC-621	1
ABC	2022	46	ABC-624,ABC-630,ABC-631	3
ABC	2022	47	ABC-646,ABC-660,ABC-674,ABC-675,ABC-676	5
ABC	2022	48	ABC-677,ABC-678,ABC-679,ABC-680,ABC-681,ABC-682,ABC-683,ABC-684,ABC-687,ABC-688,ABC-691	11
ABC	2022	49	ABC-699,ABC-700,ABC-701,ABC-702,ABC-703,ABC-704,ABC-705,ABC-706,ABC-711,ABC-712,ABC-713,ABC-731,ABC-735,ABC-736	14
ABC	2022	50	ABC-754,ABC-802,ABC-803,ABC-811	4
ABC	2022	51	ABC-835,ABC-839,ABC-843,ABC-844,ABC-845,ABC-846,ABC-848,ABC-849	8
ABC	2022	52	ABC-850,ABC-856,ABC-863,ABC-865,ABC-866	5
ABC	2022	53	ABC-879	1
ABC	2023	1		0
ABC	2023	2	ABC-898,ABC-899	2
ABC	2023	3	ABC-909,ABC-911,ABC-916,ABC-917	4
ABC	2023	4	ABC-939,ABC-941,ABC-942,ABC-970,ABC-971	5
ABC	2023	5	ABC-987,ABC-989	0
ABC	2023	6	ABC-996,ABC-1001,ABC-1002,ABC-1008	2
ABC	2023	7	ABC-1025,ABC-1026	4
ABC	2023	8	ABC-1049	2
ABC	2023	9	ABC-1091,ABC-1100,ABC-1108,ABC-1109	1
ABC	2023	10	ABC-1161	4
ABC	2023	11	ABC-1166,ABC-1167,ABC-1168,ABC-1181,ABC-1188	1
ABC	2023	12	ABC-1196	5
ABC	2023	13	ABC-1223,ABC-1224,ABC-1229,ABC-1230	1
ABC	2023	14	ABC-1271,ABC-1276	4
ABC	2023	15	ABC-1290,ABC-1291,ABC-1304,ABC-1306,ABC-1307,ABC-1308,ABC-1309	2
ABC	2023	16	ABC-1311,ABC-1327,ABC-1328,ABC-1330,ABC-1331,ABC-1332,ABC-1345	7
ABC	2023	17	ABC-1374,ABC-1375,ABC-1376,ABC-1382,ABC-1420,ABC-1421	7
ABC	2023	18	ABC-1434,ABC-1463,ABC-1464	0
ABC	2023	19	ABC-1474,ABC-1478,ABC-1483,ABC-1485	6
ABC	2023	20	ABC-1497,ABC-1504,ABC-1512	3
ABC	2023	21	ABC-1525	4
ABC	2023	22	ABC-1540	3
ABC	2023	23	ABC-1566,ABC-1581,ABC-1582,ABC-1594	1
ABC	2023	24	ABC-1619	1
ABC	2023	25		4
ABC	2023	26		1
ABC	2023	27		0
ABC	2023	28		0
ABC	2023	29	ABC-1734,ABC-1741	2
ABC	2023	30		0
ABC	2023	31		0
ABC	2023	32	ABC-1801	1
ABC	2023	33	ABC-1839,ABC-1843,ABC-1845	3
ABC	2023	34	ABC-1846	1
ABC	2023	35		0
ABC	2023	36		0
ABC	2023	37	ABC-1915	1
ABC	2023	38		0
ABC	2023	39	ABC-1942,ABC-1943,ABC-1944,ABC-1946,ABC-1955,ABC-1956	6
ABC	2023	40	ABC-1963,ABC-1968,ABC-1969,ABC-1973,ABC-1974,ABC-1979	6
ABC	2023	41		0
ABC	2023	42	ABC-2010	1
ABC	2023	43		0
ABC	2023	44	ABC-2055	1
ABC	2023	45		0
ABC	2023	46	ABC-2100	1
ABC	2023	47	ABC-2152,ABC-2153,ABC-2155	3
ABC	2023	48	ABC-2170	1
ABC	2023	49	ABC-2181,ABC-2184	2
ABC	2023	50	ABC-2187,ABC-2188,ABC-2199	3
ABC	2023	51	ABC-2227,ABC-2232	2
ABC	2023	52	ABC-2239	1
ABC	2023	53		0
ABC	2024	1		0
ABC	2024	2		0
ABC	2024	3		0
ABC	2024	4	ABC-2308,ABC-2333	2
ABC	2024	5	ABC-2341	1
ABC	2024	6		0
ABC	2024	7	ABC-2387	1

[JeevanMallya]

Thank you very much

[v-kongfanf-msft]

Hi @JeevanMallya ,

Thanks for your feedback.

Best regards,

Adamk Kong

[danextian · Accepted solution]

Hi @JeevanMallya 

Please see attached sample pbix