Outlook - Generic mailbox - slow loading

Power BI Online Service
1

Dear DNA Team,

I am seeking your advice.

I am trying to connect the generic mailbox to the Power BI. As per the below m-code, I am only interested in the data 2020 onwards plus folders that = Inbox, =Sent Items, and any folder that contains Archive.

When I press save and load 1.5h ago from this moment, the data is still loading.

Am right to say that power BI is going through every individual item in the maiblox and then applies the m code to return the emails that meets the criteria? Would it be wise to move emails from 2020 onwards to one folder?

Could you please advise what could I do to speed up the loading?

image

image
image580×529 18 KB

image

let

Source = Exchange.Contents("xxxxxx@company.com"),

Mail1 = Source{[Name="Mail"]}[Data],

#"Removed Other Columns" = Table.SelectColumns(Mail1,{"Folder Path", "Subject", "Sender", "DateTimeReceived"}),

#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Email", each Record.Field([Sender],"Address")),

#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"DateTimeReceived", type date}}),

#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sender"}),

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [DateTimeReceived] > #date(2019, 12, 31)),

#"Added Conditional Column" = Table.AddColumn(#"Filtered Rows", "Folder", each if [Folder Path] = "\Inbox\" then "Inbox" else if [Folder Path] = "\Sent Items\" then "Sent" else if Text.Contains([Folder Path], "\Archive\") then "Solved" else 1),

#"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each [Folder] <> "1"),

#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Folder", "Folder Path", "Subject", "DateTimeReceived", "Email"}),

#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Folder Path"})

in

#"Removed Columns1"

Thank you,

Mateusz

3

@sam.mckay @Melissa @BrianJ Could you please advise how could I speed up the loading?

Thank you,

Mateusz

4

@Matty,

Take a look at the following article about optimizing your.settings and turning off the Enable Parallel Loading option,

I also thought that adding a Table.Buffer step might help, but Chris Webb says it doesn’t in this case. He does suggest a higher performance config using dataflows.

Otherwise, it looks to me like you’ve done the right stuff up front in terms of quickly filtering out the rows and columns you don’t need.

Hope this is helpful. Let me know if this improves performance noticeably.

  • Brian
1 Like
5

@Matty,

Also, you should turn off Autodetect Relationships and Auto Date/Time - not because they’ll help you here, but because they’re generally terrible “features”…:stuck_out_tongue_winking_eye:

  • Brian
6

Hi @Matty, did the response provided by @BrianJhelp you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks

7

@BrianJ

It helped a bit, I also noticed that the time loading depends when I run the report,

the best time to run looks like is the evening hours when nobody uses the generic mailbox :smiley: and also the speed of the connection.

Thank you a lot for confirmation regarding the m-code and advice (terrible features are off :D)

Thank you,

Mateusz

2 Likes
8

@Matty,

Ha! I’ve come to the same conclusion on my own projects - a well-planned scheduled refresh covers a multitude of sins. :laughing:

  • Brian
1 Like