cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lamysroe Frequent Visitor
Frequent Visitor

Expanding recurring meeting rows from recurringmaster

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. image.png

 

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. 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Expanding recurring meeting rows from recurringmaster

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 Frequent Visitor
Frequent Visitor

Re: 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!

lamysroe Frequent Visitor
Frequent Visitor

Re: Expanding recurring meeting rows from recurringmaster

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 281 members 2,700 guests
Please welcome our newest community members: