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

Anonymous Bear with me if this gets long....

1. I loaded the calendar twice
   1. The first calendar I loaded, expanded attributes and filtered for single meetings
   2. The second calendar I loaded, expanded attributes and filtered for recurring meetings
      1. I expanded Last Occurence End (this is the true last date of the recurring meeting despite occurences specified or end by date)
         1. I used this date to expand the meeting dates
      2. Expand Recurrence Plus these... dayofmonth, dayofweek, daysoftheweek
         recurrence.startdate 
         recurrence.enddate   (specified end date)
         recurrence.numberofoccurences   (used if no end date specified)
         recurrence.pattern     (daily, weekly, monthly)
         recurrence.interval    (relative to pattern, number of times in a week or month for recurrence)
      3. Add DatesinPeriod Column (as list)
      4. Add Newrecurrenceinterval Column    (monthly pattern is *4 to account for the weeks in a month. All others done daily)
      5. Expand list DatesinPeriod 
      6. Add ModdInterval Column  
      7. Filter Row filter to 0 of mod diff     (Filtering to 0 provides the actual days of meetings, all other numbers are the days between meetings)
   3. I appended all single calendars into 1
   4. I appended all recurring calendars into 1

Single Calendar: There are extra steps here beecause I added columns to clarify meeting room used and office location 

let
    Source = Exchange.Contents("XXXXXX"),
    Calendar1 = Source{[Name="Calendar"]}[Data],
   #"Removed Other Columns" = Table.SelectColumns(Calendar1,{"Subject", "Location", "Start", "End", "Attributes", "Body", "Id"}),
    #"Expanded Body" = Table.ExpandRecordColumn(#"Removed Other Columns", "Body", {"TextBody"}, {"TextBody"}),
        #"Expanded Attributes" = Table.ExpandRecordColumn(#"Expanded Body", "Attributes", {"AppointmentType", "Duration", "ExtendedProperties", "Organizer", "Recurrence"}, {"Attributes.AppointmentType", "Attributes.Duration", "Attributes.ExtendedProperties", "Attributes.Organizer", "Attributes.Recurrence"}),
    #"Expanded Attributes.Organizer" = Table.ExpandRecordColumn(#"Expanded Attributes", "Attributes.Organizer", {"Name"}, {"Attributes.Organizer.Name"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Attributes.Organizer",null,"",Replacer.ReplaceValue,{"TextBody"}),
    #"ReplacedValue - Location ""meeting"" if empty" = Table.ReplaceValue(#"Replaced Value",null,"meeting",Replacer.ReplaceValue,{"Location"}),
   #"AddedColumn - Office" = Table.AddColumn( #"ReplacedValue - Location ""meeting"", "Office", each if Text.Contains([Location], "meeting") then "XXXXXCity" else "XXXXcity"),
      #"AddedColumn - Room" = Table.AddColumn(#"AddedColumn - Office", "Room", each if Text.Contains([Location], "meeting") then "XXXXXmeeting room name" else "XXXXXmeeting room name"),
    #"Filtered - ""Single""" = Table.SelectRows(#"AddedColumn - Room", each ([Attributes.AppointmentType] = "Single")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered - ""Single""",{"Attributes.ExtendedProperties", "Attributes.Recurrence"}),
    #"Renamed Column - Attributes organizer" = Table.RenameColumns(#"Removed Columns",{{"Attributes.Organizer.Name", "Name"}})
in
    #"Renamed Column - Attributes organizer"



​

Recurring Meeting Calendar: Same with additional steps as above. The recurring interval is how often the meeting happens. The recurring interval gets timesed by 7 for the days in a week. The monthly doesn't always end up on the exact date but it does count the correct number of occurences (this was the only way I could get to work). I took a meeting room calendar from Outlook and the data in Power BI and confirmed that data for an entire year to be sure I was getting it right. 

let
    Source = Exchange.Contents("XXXXXX"),
    Calendar1 = Source{[Name="Calendar"]}[Data],
 #"Removed Other Columns" = Table.SelectColumns(Calendar1,{"Subject", "Location", "Start", "End", "Attributes", "Body", "Id"}),
        #"Expanded Attributes" = Table.ExpandRecordColumn(#"Removed Other Columns", "Attributes", {"AppointmentType", "Duration", "ExtendedProperties", "LastOccurrence", "Organizer", "Recurrence"}, {"AppointmentType", "Duration", "ExtendedProperties", "LastOccurrence", "Organizer", "Recurrence"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Attributes", each ([AppointmentType] = "RecurringMaster")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"meeting",Replacer.ReplaceValue,{"Location"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Replaced Value", "Office", each if Text.Contains([Location], "meeting") then "XXXXcity" else "XXXXcity"),
      #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Room", each if Text.Contains([Location], "meeting") then "XXXXXmeeting room name" else "XXXXXmeeting room name"),
    #"Expanded Recurrence" = Table.ExpandRecordColumn(#"Added Conditional Column2", "Recurrence", {"StartDate", "EndDate", "NumberOfOccurrences", "Pattern", "Interval", "DayOfMonth", "DayOfTheWeek", "DaysOfTheWeek"}, {"Recurrence.StartDate", "Recurrence.EndDate", "Recurrence.NumberOfOccurrences", "Recurrence.Pattern", "Recurrence.Interval", "Recurrence.DayOfMonth", "Recurrence.DayOfTheWeek", "Recurrence.DaysOfTheWeek"}),
    #"Expanded ExtendedProperties" = Table.ExpandRecordColumn(#"Expanded Recurrence", "ExtendedProperties", {"RecurrencePattern"}, {"ExtendedProperties.RecurrencePattern"}),
    #"Expanded LastOccurrence" = Table.ExpandRecordColumn(#"Expanded ExtendedProperties", "LastOccurrence", {"End"}, {"LastOccurrence.End"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded LastOccurrence",{{"LastOccurrence.End", type date}, {"Recurrence.StartDate", type date}, {"Recurrence.EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "DatesInPeriod", each List.Dates([Recurrence.StartDate],Duration.Days(Duration.From([LastOccurrence.End]-[Recurrence.StartDate])),#duration(1,0,0,0))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Newrecurrenceinterval", each if [Recurrence.Pattern] = "RelativeMonthlyPattern" then [Recurrence.Interval]*4 else [Recurrence.Interval]),
    #"Expanded DatesInPeriod" = Table.ExpandListColumn(#"Added Custom2", "DatesInPeriod"),
    #"Filtered Rows3" = Table.SelectRows(#"Expanded DatesInPeriod", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows3", "DateDiffModInterval", each Number.Mod(Duration.Days(Duration.From([DatesInPeriod]-[Recurrence.StartDate])),7*[Newrecurrenceinterval])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DatesInPeriod", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "DatesInPeriod", "DatesInPeriod - Copy"),
    #"Extracted Day Name" = Table.TransformColumns(#"Duplicated Column", {{"DatesInPeriod - Copy", each Date.DayOfWeekName(_), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Day Name",{{"DatesInPeriod - Copy", "DayName"}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"Newrecurrenceinterval"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"DateDiffModInterval"}),
    #"Filtered Rows4" = Table.SelectRows(#"Replaced Value2", each [DateDiffModInterval] = 0),
    #"Filtered Rows5" = Table.SelectRows(#"Filtered Rows4", each true),
    #"Expanded Organizer" = Table.ExpandRecordColumn(#"Filtered Rows5", "Organizer", {"Name"}, {"Name"}),
    #"Expanded Body" = Table.ExpandRecordColumn(#"Expanded Organizer", "Body", {"TextBody"}, {"TextBody"})
in
    #"Expanded Body"

Appended single meetings tables: Added days of weeks and working hours to evaluate available vs used hours (I did this specific to this table because I needed for something separate)

let
    Source = Table.Combine (XXXALL SINGLE TABLES),
    #"Extracted Start Time" = Table.AddColumn(Source, "Time", each DateTime.Time([Start]), type time),
    #"Renamed Start Time" = Table.RenameColumns(#"Extracted Start Time",{{"Time", "Start Time"}}),
    #"Extracted End Time" = Table.AddColumn(#"Renamed Start Time", "Time", each DateTime.Time([End]), type time),
    #"Renamed End Time" = Table.RenameColumns(#"Extracted End Time",{{"Time", "End Time"}}),
    #"Inserted Year" = Table.AddColumn(#"Renamed End Time", "Year", each Date.Year([End]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([End]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name.1", each Date.DayOfWeekName([Start]), type text),
    #"Inserted Month Number" = Table.AddColumn(#"Inserted Day Name", "Month", each Date.Month([End]), Int64.Type),
    #"Inserted Day of Week Number" = Table.AddColumn(#"Inserted Month Number", "Day of Week", each Date.DayOfWeek([Start])+1, Int64.Type),
    #"ChangedType - Dates" = Table.TransformColumnTypes(#"Inserted Day of Week Number",{{"Start", type datetime}, {"End", type datetime}}),
    #"Added Available Work Hours" = Table.AddColumn(#"ChangedType - Dates", "Available Work Hours", each if [Day of Week] = 1 then 0 else if [Day of Week] = 7 then 0 else 10),
    #"Inserted Week of Year" = Table.AddColumn(#"Added Available Work Hours", "Week of Year", each Date.WeekOfYear([Start]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Year", "Day of Year", each Date.DayOfYear([Start]), Int64.Type),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Inserted Day of Year", {"Room"}),
    #"Changed Duration to Hours" = Table.TransformColumns(#"Removed Errors",{{"Attributes.Duration", Duration.TotalHours, type number}}),
    #"Renamed Duration" = Table.RenameColumns(#"Changed Duration to Hours",{{"Attributes.Duration", "Duration"}}),
    #"ChangedType - Dates2" = Table.TransformColumnTypes(#"Renamed Duration",{{"Start", type date}, {"End", type date}})
in
    #"ChangedType - Dates2"

Appended recurring meetings tables

let
    Source = Table.Combine(XXXXALL RECURRING TABLES),
    #"Removed RecurrenceDayofMonth(null)" = Table.RemoveColumns(Source,{"Recurrence.DayOfMonth"}),
    #"ChangedType - Start Date to Time" = Table.TransformColumnTypes(#"Removed RecurrenceDayofMonth(null)",{{"Start", type time}}),
    #"Renamed ""Start"" to ""StartTimeRec""" = Table.RenameColumns(#"ChangedType - Start Date to Time",{{"Start", "StartTimeRec"}}),
    #"ChangedType - End Date to Time" = Table.TransformColumnTypes(#"Renamed ""Start"" to ""StartTimeRec""",{{"End", type time}}),
    #"Renamed DateInPeriod(StartDate) & End(EndTimeRec)" = Table.RenameColumns(#"ChangedType - End Date to Time",{{"End", "EndTimeRec"}, {"DatesInPeriod", "StartDate"}}),
    #"ChangedType - Duration to nearest 15 minutes" = Table.TransformColumns(#"Renamed DateInPeriod(StartDate) & End(EndTimeRec)",{{"Duration", Duration.TotalHours, type number}}),
    #"Filtered Rows" = Table.SelectRows(#"ChangedType - Duration to nearest 15 minutes", each true),
    #"Removed RecurrenceEndDate" = Table.RemoveColumns(#"Filtered Rows",{"Recurrence.EndDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed RecurrenceEndDate",{{"StartTimeRec", "Start Time"}, {"EndTimeRec", "End Time"}, {"StartDate", "Start"}})
in
    #"Renamed Columns"

Appended Single final table and recurring final table: 

let
    Source = Table.Combine({#"Conference Room Recurring", #"Conference Rooms Single"}),
    #"Removed Column" = Table.RemoveColumns(Source,{"End"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Removed Column",{"Year", "Month Name", "Day Name.1", "Month", "Day of Week", "Available Work Hours", "Week of Year", "Day of Year"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Removed Columns1",{"Attributes.AppointmentType", "Recurrence.DayOfTheWeek", "Recurrence.DaysOfTheWeek", "ExtendedProperties.RecurrencePattern", "DayName"}),
    #"Inserted Year" = Table.AddColumn(#"Removed Columns2", "Year", each Date.Year([Start]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Start]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Start]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month", "Day Name", each Date.DayOfWeekName([Start]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Start])+1),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Start]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day of Year", "Week of Year", each Date.WeekOfYear([Start]), Int64.Type),
    #"Added Available Work Hours" = Table.AddColumn(#"Inserted Week of Year", "Available Work Hours", each if [Day of Week] = 1 then 0 else if [Day of Week] = 7 then 0 else 10),
    #"ReplacedValue - Appt Type ""null"" to Single" = Table.ReplaceValue(#"Added Available Work Hours",null,"Single",Replacer.ReplaceValue,{"AppointmentType"}),
    #"Added Column - Room Setup" = Table.AddColumn(#XXXXXADDED CONDITIONAL COLUMN TO ADD ROOM USES TO EACH CONFERENCE ROOM NAME),
    #"Filtered Rows" = Table.SelectRows(#"Added Column - Room Setup", each true),
    #"AddedColumn - Round Start Time" = Table.AddColumn(#"Filtered Rows", "Time Test", each if Time.Minute([Start Time])/15>3 then [Start Time]+#duration(0,0,60-Time.Minute([Start Time]),-Time.Second([Start Time])) else if Time.Minute([Start Time])/15<1 then [Start Time]+#duration(0,0,-Time.Minute([Start Time]),-Time.Second([Start Time])) else [Start Time]+#duration(0,0,30-Time.Minute([Start Time]),-Time.Second([Start Time]))),
    #"Renamed - StartTimeRounded" = Table.RenameColumns(#"AddedColumn - Round Start Time",{{"Time Test", "StartTimeRounded"}}),
    #"Changed Type StartTimeRounded to Time" = Table.TransformColumnTypes(#"Renamed - StartTimeRounded",{{"StartTimeRounded", type time}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type StartTimeRounded to Time",null,"",Replacer.ReplaceValue,{"TextBody"}),
    #"Added Video Conference" = Table.AddColumn(#"Replaced Value", "Video Conference", each if Text.Contains([Subject], "bluejeans") then "Yes" else if Text.Contains([Subject], "zoom") then "Yes" else if Text.Contains([Location], "bluejeans") then "Yes" else if Text.Contains([Location], "zoom") then "Yes" else if Text.Contains([TextBody], "bluejeans") then "Yes" else if Text.Contains([TextBody], "zoom") then "Yes" else "No")
in
    #"Added Video Conference"

I also have a holiday table that I use a calendar to look at for correct working days