Forum Discussion
Expanding recurring meeting rows from recurringmaster
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!
dax Do you happent to know if there is a way to do this, or am I looking for too much?
- cz1romanini6 years agoFrequent Visitor
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?
- lamysroe6 years ago
Advocate I
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-appointments-in/m-p/1018008#M481125
This post also details accounting for the different intervals of recurring meetings.