outlook.office365.com Refreshes Very Slowly

[Juliecal73]

Hi,

I have a table of usernames (~15000) and for each one I build out a Image url using Web.contents to access their Profile pictures in Power Query Editor.

This works great, but when I refresh, it is now taking a couple of hours because it is extremely slow at accessing outlook.office365.com to build each of the Image urls.

All the other data sources (SQL) refresh fine.

Does anyone know why it takes so long? Is there perhaps a better way of doing this?

[Juliecal73]

I am still struggling with this...

I actually only need about 300 of the Image URLs as this is who is currently active in this geographical area, but was hoping to have this dynamic so that if a new person became active in this area, we would see their image.

So the scenario becomes this:

I have Table 1 (SQL) which is a list of Names (~300)

I have Table 2 (https://outlook.office365.com/) to get the email addresses of everyone in the organization (~15000).

How can I do a merge of these tables, keeping only the Table 1 data but with their Table 2 emails (~300 records) and then after that use Web.Content to get their Profile Images from O365?

This currently fails with the error about accessing other queries before the Web.Content call.

Can I filter Table 2 by Table 1 in Power Query, do the Web.Content step (much quicker for ~300) and then Merge them?

[Juliecal73]

Any thoughts on this please?

[parry2k]

@Juliecal73 you are not loading pictures in the model, you are just creating URL, correct?

[Juliecal73]

Hi, that is correct, at least that is what I belive. I use the email address in the following code to get the unique image URL for each user from OL and load it in the table.

Web.Contents("https://outlook.office365.com", [RelativePath="/ews/Exchange.asmx/s/GetUserPhoto", Query=[email=[organizationalPerson.mail], size="HR120x120"]])

 

Then I mark it as an Image URL type and it displays the profile images in a table visual.

The process of updating this table (only 5 olumns) now takes hours when it used to take minutes.

Previously I was doing it on a table with only about 150 records, and that took a while, but not bad.

But now with 15000 records...