Expanding recurring meeting rows from recurringmaster

[lamysroe]

I have all my company Exchange calendars linked with Power BI. I am able to pull all the meeting information for analytics, but am stuck on recurring meetings. Without true representation of recurring meetings, my utilization rates are inaccurate. 

1) I have expanded attributes to get recurring meeting data. 

2) I used the following script to expand the recurringmaster meetings. Doing this expanded all days between meetings and the final filter gives me meetings with a recurrence pattern for weekly. 

let
    Source = Exchange.Contents("XXXXX"),
    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"

What I can't figure out, is how to expand my rows then account for the variations in meeting intervals (1,2,3) and patterns (weekly, monthly). 

Any help would be greatly appreciated. 

[dax]

Hi lamysroe, 

I am not clear about your requirement, so if possible, could you please explain "What I can't figure out, is how to expand my rows then account for the variations in meeting intervals (1,2,3) and patterns (weekly, monthly)" to me ? What expecting out did you want to get? Then I will help you correctly.

In addition, did you want to know meaning of steps in M code?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lamysroe]

@dax My requirements are to account for all meetings on the Exchange calendars from all conference rooms in order to get statistics on utilization rates per room and as a whole, times of day usage, days of week usage, room types being most used, etc. I am stuck on being able to expand recurring meetings that would account for all types of recurring meetings past weekly meetings. I need to be able to account for recurring patterns of weekly and monthly, while also accounting for intervals of 1 (week or month), 2 (every 2 weeks), or 3 (every 3 weeks). I need the final output to only show just the meeting dates and not every single day between start date or recurring meeting and end date of recurring meeting. 

In the above, all meetings would have recurrence start and recurrence end dates. A bonus would be expanding a recurring meeting that is driven by start date and number of occurences (in this case, there would be no recurrence end date. 

Knowing the meaning of the steps in M code would be helpful, but even without it, I can dissect the code and mostly figure it out. 

Here is my sample data showing recurring meetings as expanded. It is filtered to "0" which shows weekly recurring meetings, but that is as far as I can get. I blacked out the last two columns since it was just me messing around trying to figure out how to account for patterns other than recurring weekly. Here is the code for how I got there. 

let
    Source = Exchange.Contents("XXXXXX"),
    Calendar1 = Source{[Name="Calendar"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(Calendar1,{"Location", "Start", "End", "Attributes", "Id"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"Wildlife",Replacer.ReplaceValue,{"Location"}),
 #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Office", each if Text.Contains([Location], "bluejeans") then "DC" else "DC"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Room", each if Text.Contains([Location], "bluejeans") then "Meeting Room A" else "Meeting Room A"),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom", each if Text.Contains([Location], "bluejeans") then "Yes" else "No"),
    #"Expanded Attributes" = Table.ExpandRecordColumn(#"Added Conditional Column2", "Attributes", {"AppointmentType", "IsRecurring", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.IsRecurring", "Attributes.Recurrence"}),
      #"Expanded Attributes.Recurrence" = Table.ExpandRecordColumn(#"Expanded Attributes", "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])),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Custom3", "Custom.1", each if [Attributes.Recurrence.Pattern] = "RelativeMonthlyPattern" then 30 else if [Attributes.Recurrence.Pattern] = "WeeklyPattern" then 7 else null),
    #"Added Custom4" = Table.AddColumn(#"Added Conditional Column3", "Interval calculated", each [Custom.1] * [Attributes.Recurrence.Interval]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([DateDiffModInterval] = 0))
in
    #"Filtered Rows"

Thanks!

[lamysroe]

@dax Do you happent to know if there is a way to do this, or am I looking for too much?

[cz1romanini]

I am also facing the same issue. I have now all my calendar connected to the PowerBI, but the recurring meetings does not appear on the report besides the first ocurrence... Does anybody knows how to fix that?

[lamysroe]

@cz1romanini The first script in this post shows how to expand recurring meetings. For a full breakdown of this, please see the following post where I detail out the steps I took to get all single and recurring meetings accounted for. https://community.powerbi.com/t5/Desktop/Transforming-Exchange-server-data-with-recurring-appointmen...

This post also details accounting for the different intervals of recurring meetings.