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

[raksha_v]

I have a couple of questions about pulling Exchange server data with recurring appointments into Power BI

1. Is there a way to get recurring appointments as separate appointments for each occurrence in Power BI > Power Query ? Right now recurring appointments are returned as a single appointment at first occurrence.

1. Also, how can I use the data in attributes section to generate new rows for individual occurrences and append them to the main data?

[pedanticpad]

This works perfectly where only one day per week is selected when the appointment is made in Outlook.  However if someone sets a recurring for anything more than one day a week only the first day is reporting using the above methods.

For example if a recurring appointment is created for Monday and Thursday every week, the recurrance pattern = WeeklyPattern, the recurrance interval = 1 and only the Monday is being reported, the 2nd and any subsequent days in the week included in the recurrance is not captured beause of multiplying the Interval * 7.  Is there any way to do this so that all days in the recurrance are reported?

[lamysroe]

@pedanticpad I used a seperate table that I later added to the main table. For daily recurrence I changed the recurrence pattern to daily and the interval as needed. 

[pedanticpad]

@lamysroe  Thanks for the reply.  Not sure is it the same in other versions, but in Office365 when I set up any combination of days in a week it stores this as a weekly recurrance.  Mon & Thurs = Weekly, Mon & Thurs & Fri = Weekly.  How can I account for this?  Am I just missing expanding an attribute?

[v-sihou-msft · Accepted solution]

@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("xxxxxx@microsoft.com"),
    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("xxxxx@microsoft.com"),
    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,

[lamysroe]

Is there a way to do this taking into consideration different recurrence intervals 1, 2, 3, etc. as well as different patterns (daily, weekly, monthly)?

[Anonymous]

@lamysroe  - did you ever figure this out? I am working on the same problem. Thx.

[lamysroe]

@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

[LongNguyen123]

Thank you. 
Your code helped me a lot. I also found out the correct way to import the Recurrence Calendar into PBI. I just did some options: every month, other month, and every 3 months. 

Just leave here in here if anyone needs to take a look.

let
Source = Exchange.Contents("Shared Canlendar"),
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"}),
#"Expanded Recurrence" = Table.ExpandRecordColumn(#"Replaced Value", "Recurrence", {"StartDate", "EndDate", "HasEnd", "NumberOfOccurrences", "Pattern", "Interval", "DayOfMonth", "DayOfTheWeek", "DayOfTheWeekIndex", "DaysOfTheWeek", "FirstDayOfWeek", "Month"}, {"Recurrence.StartDate", "Recurrence.EndDate", "Recurrence.HasEnd", "Recurrence.NumberOfOccurrences", "Recurrence.Pattern", "Recurrence.Interval", "Recurrence.DayOfMonth", "Recurrence.DayOfTheWeek", "Recurrence.DayOfTheWeekIndex", "Recurrence.DaysOfTheWeek", "Recurrence.FirstDayOfWeek", "Recurrence.Month"}),
#"Expanded ExtendedProperties" = Table.ExpandRecordColumn(#"Expanded Recurrence", "ExtendedProperties", {"RecurrencePattern"}, {"ExtendedProperties.RecurrencePattern"}),
#"Expanded LastOccurrence" = Table.ExpandRecordColumn(#"Expanded ExtendedProperties", "LastOccurrence", {"End"}, {"LastOccurrence.End"}),
#"Filtered Rows1 - filter Ruccurence schedule" = Table.SelectRows(#"Expanded LastOccurrence", each ([Recurrence.Pattern] = "RelativeMonthlyPattern")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1 - filter Ruccurence schedule",{{"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))),
#"Expanded DatesInPeriod" = Table.ExpandListColumn(#"Added Custom - *", "DatesInPeriod"),
#"Removed Other Columns2" = Table.SelectColumns(#"Expanded DatesInPeriod",{"Subject", "Recurrence.Interval", "Recurrence.DayOfTheWeek", "Recurrence.DayOfTheWeekIndex", "DatesInPeriod"}),
#"Filtered Rows3" = Table.SelectRows(#"Removed Other Columns2", each true),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"DatesInPeriod", type date}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each true),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows2",{"Subject", "Recurrence.Interval", "Recurrence.DayOfTheWeek", "Recurrence.DayOfTheWeekIndex", "DatesInPeriod"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns1",{{"DatesInPeriod", "Start"}, {"Recurrence.DayOfTheWeekIndex", "DayOfTheWeekIndex"}, {"Recurrence.DayOfTheWeek", "DayOfTheWeek"}, {"Recurrence.Interval", "Interval"}}),
#"Added Custom7- **" = Table.AddColumn(#"Renamed Columns1", "Custom", each let // set variable for is Order day of week in a month based on the column START
// Monday
FirstMon = Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Start]),6),1),
SecondMon = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),14),Day.Monday),
ThirdMon = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),21),Day.Monday),
FourthMon = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),28),Day.Monday),
LastMon = Date.StartOfWeek(Date.EndOfMonth([Start]),1),
// Tuesday
FirstTue = Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Start]),6),2),
SecondTue = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),14),Day.Tuesday),
ThirdTue = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),21),Day.Tuesday),
FourthTue = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),28),Day.Tuesday),
LastTue = Date.StartOfWeek(Date.EndOfMonth([Start]),2),
//Wednesday
FirstWed = Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Start]),6),3),
SecondWed = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),14),Day.Wednesday),
ThirdWed = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),21),Day.Wednesday),
FourthWed = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),28),Day.Wednesday),
LastWed = Date.StartOfWeek(Date.EndOfMonth([Start]),3),
//Thurday
FirstThu = Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Start]),6),4),
SecondThu = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),14),Day.Thursday),
ThirdThu = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),21),Day.Thursday),
FourthThu = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),28),Day.Thursday),
LastThu = Date.StartOfWeek(Date.EndOfMonth([Start]),4),
//Friday
FirstFri = Date.StartOfWeek(Date.AddDays(Date.StartOfMonth([Start]),6),5),
SecondFri = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),14),Day.Friday),
ThirdFri = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),21),Day.Friday),
FourthFri = Date.StartOfWeek(#date(Date.Year([Start]),Date.Month([Start]),28),Day.Friday),
LastFri = Date.StartOfWeek(Date.EndOfMonth([Start]),5)
in // find all the correct recurring date
// Monday
if [DayOfTheWeek] ="Monday" and [DayOfTheWeekIndex] = "First" and [Start] = FirstMon then "Yes" else 
if [DayOfTheWeek] ="Monday" and [DayOfTheWeekIndex] = "Second" and [Start] = SecondMon then "Yes" else
if [DayOfTheWeek] ="Monday" and [DayOfTheWeekIndex] = "Third" and [Start] = ThirdMon then "Yes" else
if [DayOfTheWeek] ="Monday" and [DayOfTheWeekIndex] = "Fourth" and [Start] = FourthMon then "Yes" else
if [DayOfTheWeek] ="Monday" and [DayOfTheWeekIndex] = "Last" and [Start] = LastMon then "Yes" else 

// Tuesday
if [DayOfTheWeek] ="Tuesday" and [DayOfTheWeekIndex] = "First" and [Start] = FirstTue then "Yes" else 
if [DayOfTheWeek] ="Tuesday" and [DayOfTheWeekIndex] = "Second" and [Start] = SecondTue then "Yes" else
if [DayOfTheWeek] ="Tuesday" and [DayOfTheWeekIndex] = "Third" and [Start] = ThirdTue then "Yes" else
if [DayOfTheWeek] ="Tuesday" and [DayOfTheWeekIndex] = "Fourth" and [Start] = FourthTue then "Yes" else
if [DayOfTheWeek] ="Tuesday" and [DayOfTheWeekIndex] = "Last" and [Start] = LastTue then "Yes" else

//Wednesday
if [DayOfTheWeek] ="Wednesday" and [DayOfTheWeekIndex] = "First" and [Start] = FirstWed then "Yes" else 
if [DayOfTheWeek] ="Wednesday" and [DayOfTheWeekIndex] = "Second" and [Start] = SecondWed then "Yes" else
if [DayOfTheWeek] ="Wednesday" and [DayOfTheWeekIndex] = "Third" and [Start] = ThirdWed then "Yes" else
if [DayOfTheWeek] ="Wednesday" and [DayOfTheWeekIndex] = "Fourth" and [Start] = FourthWed then "Yes" else
if [DayOfTheWeek] ="Wednesday" and [DayOfTheWeekIndex] = "Last" and [Start] = LastWed then "Yes" else 

//Thurday
if [DayOfTheWeek] ="Thursday" and [DayOfTheWeekIndex] = "First" and [Start] = FirstThu then "Yes" else 
if [DayOfTheWeek] ="Thursday" and [DayOfTheWeekIndex] = "Second" and [Start] = SecondThu then "Yes" else
if [DayOfTheWeek] ="Thursday" and [DayOfTheWeekIndex] = "Third" and [Start] = ThirdThu then "Yes" else
if [DayOfTheWeek] ="Thursday" and [DayOfTheWeekIndex] = "Fourth" and [Start] = FourthThu then "Yes" else
if [DayOfTheWeek] ="Thursday" and [DayOfTheWeekIndex] = "Last" and [Start] = LastThu then "Yes" else

//Friday
if [DayOfTheWeek] ="Friday" and [DayOfTheWeekIndex] = "First" and [Start] = FirstFri then "Yes" else 
if [DayOfTheWeek] ="Friday" and [DayOfTheWeekIndex] = "Second" and [Start] = SecondFri then "Yes" else
if [DayOfTheWeek] ="Friday" and [DayOfTheWeekIndex] = "Third" and [Start] = ThirdFri then "Yes" else
if [DayOfTheWeek] ="Friday" and [DayOfTheWeekIndex] = "Fourth" and [Start] = FourthFri then "Yes" else
if [DayOfTheWeek] ="Friday" and [DayOfTheWeekIndex] = "Last" and [Start] = LastFri then "Yes" else ""),
#"Filtered Rows5" = Table.SelectRows(#"Added Custom7- **", each ([Custom] = "Yes")),
#"Grouped Rows" = Table.Group(#"Filtered Rows5", {"Subject"}, {{"Count", each _, type table [Subject=nullable text, Interval=number, DayOfTheWeek=nullable text, DayOfTheWeekIndex=nullable text, Start=nullable date, 2nd Tuesday=date, 1st Wed=date, 2nd Wed=date, Last Wed=date, Custom=text]}}),
#"Added Custom9" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom9", "Custom", {"Interval", "Start", "Index"}, {"Custom.Interval", "Custom.Start", "Custom.Index"}),
#"Added Custom10 - *" = Table.AddColumn(#"Expanded Custom", "Custom", each if[Custom.Interval] = 4 and (List.Contains({1,5,9},[Custom.Index])) then "Yes" else 
if[Custom.Interval] =3 and (List.Contains({1,4,7,10},[Custom.Index])) then "Yes" else 
if[Custom.Interval] =2 and (List.Contains({1,3,5,7,9,11},[Custom.Index])) then "Yes" else
if[Custom.Interval] =1 then "Yes" else "No"),
#"Filtered Rows4" = Table.SelectRows(#"Added Custom10 - *", each ([Custom] = "Yes")),
#"Renamed Columns3" = Table.RenameColumns(#"Filtered Rows4",{{"Custom.Start", "Start"}}),
#"Removed Other Columns3" = Table.SelectColumns(#"Renamed Columns3",{"Subject", "Start"}),
#"Added Custom6" = Table.AddColumn(#"Removed Other Columns3", "End", each [Start]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom6",{{"Start", type date}, {"End", type date}})
in
#"Changed Type2"

[MartaS]

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!

[JuanG]

Did you find a solution to edit a single event from a recurring meeting and keep Power BI updated and sync. If yes, can you please share it? 

Thanks!

[cz1romanini]

This is really intersting, wonderful! At the same time, I would say that it is a bit complex, specially for somebody like me that is not used to work so properly with Power Bi.

I did everything as described and, in my case, I am facing the follow error message:
"Expression.Error: We cannot convert the value null to type Number.
Details:
Value=
Type=[Type]"

I have tried to fix that but I don´t have any idea about what is happening. Could you help me again, please?
Thank you sooo much!

Cesar Romanini

[lamysroe]

@cz1romanini  Go to the column that's giving the error and replace value "null" with blank. 

[SimonNicholson]

im also getting that error, it appears to be on an entire line

[lamysroe]

@SimonNicholson are able to see which column and column type is throwing the error?

[SimonNicholson]

hi

thats the stange thing, its not column its an entire row

[lamysroe]

Are you able to send a screen shot? What is the error that is being thrown? 

[SimonNicholson]

[lamysroe]

@SimonNicholson This error is from one of the columns with a Number Type that has "null" in the column rather than a blank. Go each column that has Number Type and replace "Null" with blank (as is nothing). This should clear the error. 

[SimonNicholson]

HI - already done that on all columns

[lamysroe]

Any chance you can share the file with me? If not, any chance you delete that one row just to see if works?