Transforming Exchange server data with recurring appointments in Power BI/ Power Query

raksha_v

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,