Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
raksha_v
Regular Visitor

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

‎03-01-2017 03:05 PM

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?
Labels:
Message 1 of 28
20,398 Views
0
1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft Employee
Microsoft Employee

‎03-06-2017 04:45 AM

@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"

77.PNG

 

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"

777.PNG

 

 

Regards,

View solution in original post

Message 2 of 28
21,154 Views
27 REPLIES 27
SimonNicholson
Frequent Visitor
In response to lamysroe

‎07-21-2020 01:45 PM

SimonNicholson_0-1595364280546.png

 

Message 13 of 28
12,137 Views
0
lamysroe
Advocate I
Advocate I
In response to SimonNicholson

‎07-22-2020 05:24 AM

@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. 

Message 14 of 28
12,125 Views
0
SimonNicholson
Frequent Visitor
In response to lamysroe

‎07-22-2020 05:27 AM

HI - already done that on all columns

Message 15 of 28
12,069 Views
0
lamysroe
Advocate I
Advocate I
In response to SimonNicholson

‎07-22-2020 05:32 AM

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

Message 17 of 28
12,066 Views
0
SimonNicholson
Frequent Visitor
In response to lamysroe

‎07-22-2020 06:06 AM

hi - think i figured it out

 

if the recurrence doesnt have an end date then it errors. so i added in 31/12/2025 and it runs

 

thanks for the suggestions

 

 

Message 18 of 28
10,770 Views
0
lamysroe
Advocate I
Advocate I
In response to SimonNicholson

‎07-22-2020 05:30 AM

What step on the side does the error occur? Are you able to back out to the step and narrow down the change the created the issue?

Message 16 of 28
8,125 Views
0
Anonymous
Not applicable
In response to lamysroe

‎04-10-2020 07:07 AM

@lamysroe Holy smokes - thanks for the detailed response!

I will need to take some time to review in more detail. (I am not a Power BI expert and work on this when I get time.) Especially the part about Available vs. Used hours. (For that I am currently using separate tabs for each month of the year and assigning a number of available hours to the tab and filtering all visuals on the tab for that particular month.)

I took a similar approach to the queries - i.e. break apart the original data into different transforms and then reassemble them into one at the end.

With regards to Daily recurrences using a number of recurrences vs. an end date, I created a duplicate column for the start date, converted that into a whole number and then added the number of recurrences from the expanded attributes 'recurrence.numberofoccurrences'. I duplicated that column and then converted it back to a date type. This gives me the end date of the recurrence and then I can use DatesInPeriod, expand and ModDiff like you did.

Message 6 of 28
7,555 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All