Use PowerQuery to generate a list of items based on a date recurrence

[Dan_W1]

I have a Sharepoint calendar with recurring items which I want to export into a CSV, however Sharepoint only exports recurring events as a single item rather than a row for each recurrence.

I connected the list to Power BI and found the RecurrenceData field which shows the recurrence methodology. Can someone help me with how to generate the list of individual rows using this information?

Note - there are different recurrences for items in the list. E.g. "annually on the x day of the month", "weekly on x day", "monthly on the x day of the month"

Below is an example of list data I am working with:

 

[alansidman]

We cannot manipulate data in a picture. Please reload your sample using XL2BB. See my signature for instructions. Also, mock up what you want you solution to look like

 

[Dan_W1]

We cannot manipulate data in a picture. Please reload your sample using XL2BB. See my signature for instructions. Also, mock up what you want you solution to look like

Thanks for your reply. Please see XL2BB included below:

This is the data from the Sharepoint list (Column H will not be in the data, I have added this to describe Column G for this post):

Book1(44).xlsx
	A	B	C	D	E	F	G	H
1	Title	EventDate	EndDate	fAllDayEvent	fRecurrence	ID.1	FieldValuesForEdit.RecurrenceData	Recurrence Note
2	Task 1	1/11/2021	17/12/2040	FALSE	TRUE	1	<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><weekly mo="TRUE" weekFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>	Weekly - First day of the week
3	Task 2	5/11/2021	2/01/2105	TRUE	TRUE	5	<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><monthlyByDay fr="TRUE" weekdayOfMonth="first" monthFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>	Monthly - First Friday of the month
4	Task 3	12/11/2021	9/01/2105	TRUE	TRUE	3	<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><monthlyByDay fr="TRUE" weekdayOfMonth="second" monthFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>	Monthly - Second Friday of the month
5	Task 4	19/11/2021	16/01/2105	TRUE	TRUE	4	<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><monthlyByDay fr="TRUE" weekdayOfMonth="third" monthFrequency="1" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>	Monthly - Third Friday of the month
6	Task 5	30/11/2021	30/11/2170	TRUE	TRUE	6	<recurrence><rule><firstDayOfWeek>su</firstDayOfWeek><repeat><yearly yearFrequency="1" month="11" day="30" /></repeat><repeatForever>FALSE</repeatForever></rule></recurrence>	Annually - On the 30 November
Sheet3

Here is what I would like the solution to look like in Nov/Dec 2021 and Nov/Dec 2022 as an example:

Book1(44).xlsx
	A	B
9	Sample - Nov/Dec 2021
10	Title	EventDate
11	Task 1	1/11/2021
12	Task 1	11/8/21
13	Task 1	11/15/21
14	Task 1	11/22/21
15	Task 1	11/29/21
16	Task 1	12/6/21
17	Task 1	12/13/21
18	Task 1	12/20/21
19	Task 1	12/27/21
20	Task 2	5/11/2021
21	Task 2	12/3/21
22	Task 3	12/11/2021
23	Task 3	12/10/21
24	Task 4	19/11/2021
25	Task 4	12/17/21
26	Task 5	30/11/2021
27
28	Sample Nov/Dec 2022
29	Title	EventDate
30	Task 1	11/7/22
31	Task 1	11/14/22
32	Task 1	11/21/22
33	Task 1	11/28/22
34	Task 1	12/5/22
35	Task 1	12/12/22
36	Task 1	12/19/22
37	Task 1	12/26/22
38	Task 2	11/4/22
39	Task 2	12/2/22
40	Task 3	11/11/22
41	Task 3	12/9/22
42	Task 4	11/18/22
43	Task 4	12/16/22
44	Task 5	11/30/22
Sheet3