How to save a sorted query to the data model (for performance reasons in Power Pivot)

[RichardS]

Hi, I would like to save a sorted query to the data model in Excel 2016 x64. The motivation has to do with performance improvements in Power Pivot, see the blog post sort-data-load-improved-compression on powerpivotpro. However, when applying sort in Power Query, it seems to not save the table in a sorted way into the data model due to lazy evaluation. I’m using Power Query to import the data from a folder of .csv files and then unpivot them. Do you have any idea how to make Power Query actually save the query to the data model in the specified order? If not, do you know of a suitable work-around? Thanks and best regards, Richard

 

[Ken Puls]

The only thing I can think of here is to add one final step to the end of the query chain that applies Table.Buffer(). That has the effect of reading it into memory, so will slow down the load a bit, but should lock in your sort order.

So if your last step is "Changed Type", you'd need a new step that is =Table.Buffer(#"Changed Type")

Hope that helps,

 

[RichardS]

The only thing I can think of here is to add one final step to the end of the query chain that applies Table.Buffer(). That has the effect of reading it into memory, so will slow down the load a bit, but should lock in your sort order. So if your last step is "Changed Type", you'd need a new step that is =Table.Buffer(#"Changed Type") Hope that helps,

Thank you very much for your reply. I think that the Table.Buffer step and other methods like adding indexes do not cause Power Query to rearrange the data internally. Here is why: When you sort a table in Power Query and load it to Excel, the table is properly sorted in Excel. If, however, you additionally add the query to the data model, the table in Excel gets into a seemingly arbitrary sort order. This is what I presume the order in memory to be. Do you have any other idea on how to solve this sort issue? Best regards, Richard

 

[Ken Puls]

It it's coming out correctly in Excel, then I'd say that Power Query is doing it's job and the issue is probably on the Power Pivot side. I'd still try the Table.Buffer just to see if it affects things, but it most likely won't.

If Power Pivot is rearranging the data, that's happening post Power Query. And honestly, I'm truly not sure how to stop that from happening. Power Pivot is supposed to deal with all the data optimization on it's own.