Refresh taking too long when using AddIndexColumn and Expand Lists

[Anonymous]

Hi all, 

A person I work with and I encountered a problem on PowerBI using a Microsoft Exchange source.

Some of the "Id" he got weren't unique, so he decided to create an Index using AddIndexColumn.

He then needed to Expand the Lists in the column "Categories". However, the refresh  was taking too much time. (1min30 without expand, more than 2 hours with the expand)

After some tests, we found out that the Expand wasn't a problem by himself. The refresh was fast enough when doing the expand, as long as we were not using AddIndexColumn before.

We can create an Index by concatenating the contents of some others columns, but i was wondering why the AddIndexColumn was slowing the refresh that much.

If anyone has any informations on that subject... 🙂

Thanks !

[edhans]

Try wrapping the statement immediately before adding the index with Table.Buffer() and see if that helps. 

[Anonymous]

Hi edhans,

Thank you for your answer.

I never used Table.Buffer() before.

I tried using it, but it is even slower. I should have informed you that I have 1GB of data in my mailbox, and the person i'm working with has 7 distincts mailbox that can contain 33GB (max) each. Unfortunately I don't think the memory can handle that, even if some columns are deleted before using the Table.Buffer().

The only fast way of creating an index i've found here is to concatenate several fields.

With AddIndexColumns before the expand, when doing an Apply, it treats 400 rows almost instantly, and then they are treated 10 by 10.

Is there a temporary storing of data on the hard drive involved ?

[edhans]

Yes. Power Query caches data, but that speeds up development. It doesn't impact the report as the cache isn't used during the actual refresh.