Forum Discussion
Transforming Exchange server data with recurring appointments in Power BI/ Power Query
I have a couple of questions about pulling Exchange server data with recurring appointments into Power BI
- Is there a way to get recurring appointments as separate appointments for each occurrence in Power BI > Power Query ? Right now recurring appointments are returned as a single appointment at first occurrence.
- Also, how can I use the data in attributes section to generate new rows for individual occurrences and append them to the main data?
When connecting Exchange data, it only has one record for an appointment, no matter it's Single or RecurringMaster. You can expand the attributes columns into the MeetingRequest or Calendar table.
let Source = Exchange.Contents("[email protected]"), Calendar1 = Source{[Name="Calendar"]}[Data], #"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.Recurrence"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Attributes", each ([Attributes.AppointmentType] = "RecurringMaster")), #"Expanded Attributes.Recurrence" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes.Recurrence", {"StartDate", "EndDate", "Pattern", "Interval"}, {"Attributes.Recurrence.StartDate", "Attributes.Recurrence.EndDate", "Attributes.Recurrence.Pattern", "Attributes.Recurrence.Interval"}) in #"Expanded Attributes.Recurrence"It's not a good practice to have those recurring appointments appeared in the calendar table, because you need to genereate a list of dates between start date and end date for each recurring appointment. It will be a huge table. Then calculate the datediff and mod interval to get those recurring days for each appointment. And it can't work for appointments with NO END DATE. Please see my sample below, I started with above filtered Recurring Appointments records table:
let Source = Exchange.Contents("[email protected]"), Calendar1 = Source{[Name="Calendar"]}[Data], #"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.Recurrence"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Attributes", each ([Attributes.AppointmentType] = "RecurringMaster")), #"Expanded Attributes.Recurrence" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes.Recurrence", {"StartDate", "EndDate", "Pattern", "Interval"}, {"Attributes.Recurrence.StartDate", "Attributes.Recurrence.EndDate", "Attributes.Recurrence.Pattern", "Attributes.Recurrence.Interval"}), #"Added Custom" = Table.AddColumn(#"Expanded Attributes.Recurrence", "RecurrenceStartDate", each [Attributes.Recurrence.StartDate]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "RecurrenceEndDate", each [Attributes.Recurrence.EndDate]), #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([RecurrenceEndDate] <> null)), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"RecurrenceStartDate", type date}, {"RecurrenceEndDate", type date}}), #"Added Custom2" = Table.AddColumn(#"Changed Type", "DatesInPeriod", each List.Dates([RecurrenceStartDate],Duration.Days(Duration.From([RecurrenceEndDate]-[RecurrenceStartDate])),#duration(1,0,0,0))), #"Expanded DatesInPeriod" = Table.ExpandListColumn(#"Added Custom2", "DatesInPeriod"), #"Added Custom3" = Table.AddColumn(#"Expanded DatesInPeriod", "DateDiffModInterval", each Number.Mod(Duration.Days(Duration.From([DatesInPeriod]-[RecurrenceStartDate])),7*[Attributes.Recurrence.Interval])), #"Filtered Rows2" = Table.SelectRows(#"Added Custom3", each ([DateDiffModInterval] = 0)) in #"Filtered Rows2"Regards,
27 Replies
When connecting Exchange data, it only has one record for an appointment, no matter it's Single or RecurringMaster. You can expand the attributes columns into the MeetingRequest or Calendar table.
let Source = Exchange.Contents("[email protected]"), Calendar1 = Source{[Name="Calendar"]}[Data], #"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.Recurrence"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Attributes", each ([Attributes.AppointmentType] = "RecurringMaster")), #"Expanded Attributes.Recurrence" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes.Recurrence", {"StartDate", "EndDate", "Pattern", "Interval"}, {"Attributes.Recurrence.StartDate", "Attributes.Recurrence.EndDate", "Attributes.Recurrence.Pattern", "Attributes.Recurrence.Interval"}) in #"Expanded Attributes.Recurrence"It's not a good practice to have those recurring appointments appeared in the calendar table, because you need to genereate a list of dates between start date and end date for each recurring appointment. It will be a huge table. Then calculate the datediff and mod interval to get those recurring days for each appointment. And it can't work for appointments with NO END DATE. Please see my sample below, I started with above filtered Recurring Appointments records table:
let Source = Exchange.Contents("[email protected]"), Calendar1 = Source{[Name="Calendar"]}[Data], #"Expanded Attributes" = Table.ExpandRecordColumn(Calendar1, "Attributes", {"AppointmentType", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.Recurrence"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Attributes", each ([Attributes.AppointmentType] = "RecurringMaster")), #"Expanded Attributes.Recurrence" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes.Recurrence", {"StartDate", "EndDate", "Pattern", "Interval"}, {"Attributes.Recurrence.StartDate", "Attributes.Recurrence.EndDate", "Attributes.Recurrence.Pattern", "Attributes.Recurrence.Interval"}), #"Added Custom" = Table.AddColumn(#"Expanded Attributes.Recurrence", "RecurrenceStartDate", each [Attributes.Recurrence.StartDate]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "RecurrenceEndDate", each [Attributes.Recurrence.EndDate]), #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([RecurrenceEndDate] <> null)), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"RecurrenceStartDate", type date}, {"RecurrenceEndDate", type date}}), #"Added Custom2" = Table.AddColumn(#"Changed Type", "DatesInPeriod", each List.Dates([RecurrenceStartDate],Duration.Days(Duration.From([RecurrenceEndDate]-[RecurrenceStartDate])),#duration(1,0,0,0))), #"Expanded DatesInPeriod" = Table.ExpandListColumn(#"Added Custom2", "DatesInPeriod"), #"Added Custom3" = Table.AddColumn(#"Expanded DatesInPeriod", "DateDiffModInterval", each Number.Mod(Duration.Days(Duration.From([DatesInPeriod]-[RecurrenceStartDate])),7*[Attributes.Recurrence.Interval])), #"Filtered Rows2" = Table.SelectRows(#"Added Custom3", each ([DateDiffModInterval] = 0)) in #"Filtered Rows2"Regards,
This works perfectly where only one day per week is selected when the appointment is made in Outlook. However if someone sets a recurring for anything more than one day a week only the first day is reporting using the above methods.
For example if a recurring appointment is created for Monday and Thursday every week, the recurrance pattern = WeeklyPattern, the recurrance interval = 1 and only the Monday is being reported, the 2nd and any subsequent days in the week included in the recurrance is not captured beause of multiplying the Interval * 7. Is there any way to do this so that all days in the recurrance are reported?
pedanticpad I used a seperate table that I later added to the main table. For daily recurrence I changed the recurrence pattern to daily and the interval as needed.
lamysroe Thanks for the reply. Not sure is it the same in other versions, but in Office365 when I set up any combination of days in a week it stores this as a weekly recurrance. Mon & Thurs = Weekly, Mon & Thurs & Fri = Weekly. How can I account for this? Am I just missing expanding an attribute?
