Forum Discussion

raksha_v's avatar
raksha_v
Regular Visitor
9 years ago
Solved

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 occurrenc...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    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,

MartaS's avatar
MartaS
New Member
3 years ago

Hi - that is an interesting soliution!

 

However:

- if someone edits single meeting in a cycle and changes date of this meeting (it is case, when a single meeting date was edited - rest of meetings in a cycle remain unchanged)

- how do you see this?

- I think using your method you will not see such case

- the only thing you will see is calculated date of the meeting 9based on recurrence assumptions)

 

Does anyone know how to fix this case? My goal is to see all dates of cyclical meetings (the real dates, as I see them in calendar)

 

Thanks!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Marta, I haven't figuree out a way to automatically sniff out modifictions  but I did find that if a recurring event is modified, I can find the dates pre- generated in the  Attributes.Modified Occurences table. I just wish I could find it like this even when they aren't modified. Anyway, I find myself breaking out the modified occurrences in their own queries and then appending them back together with the rest, along the lines of iamysore's ortigina strategy.
#"Expanded Attributes.ModifiedOccurrences" = Table.ExpandTableColumn(#"Expanded Attributes.Recurrence", "Attributes.ModifiedOccurrences", {"Start", "End", "OriginalStart"}, {"Attributes.ModifiedOccurrences.Start", "Attributes.ModifiedOccurrences.End", "Attributes.ModifiedOccurrences.OriginalStart"}),