Power Query - Sorting is Sometimes Pointless (VertiPaq Insights)

Understand why sometimes your Power Query sorts are not retained in Excel or Power BI. Learn why this is a non-issue and how to "override" it when dealing with Excel tables as the output. Also, we get an overview of the compression and optimization strategies of the VertiPaq engine when working with data models.

File Download Link:

00:27 Demonstrating the Issue
02:53 VertiPaq Engine (Overview)
04:17 Value Encoding (Example)
05:35 Dictionary Encoding (Example)
06:46 Run Length Encoding (REL) (Example)
07:56 Dual Encoding (Example)
08:54 Moral of the Story
09:25 Sorting does have a purpose in Power Query
09:47 When you SHOULD sort for output
12:03 Fixing "Broken" Sorts
12:25 Refreshing Sorted Data in Excel
13:04 Microsoft's Proprietary Logic

[Quidisi]

This was helpful.
True story - I'm so dumb, that until recently, I used to spend time in PQ sorting AND arranging columns. Did a lot of things that broke query folding and caused slowdowns. Then one day I asked myself, why am I trying to get my data all neat in PQ? Who cares... take care of it in the visuals.
But as you said, sometimes sorting is necessary to further transform your data - but apart from that, I stopped sorting and I stopped arranging columns.

[Rice]

Great video of explanation of engine mechanism.😏
Thank you!🤗

[kkravch]

Thanks, very helpful, professor. It seems to be the reason why we need Table.Buffer when Group by depends on sort order...

[ahmedshalaby]

Greetings from Egypt, since the first time I saw I video for you I decided to watch whatever are your posting on YT because you are smart

[JuanCVazquez-sk]

Awesome! Very interesting and helpful. Thanks Professor.

[kebincui]

Great video which clear the unclear issue of sorting and removing duplicate row to get the latest record. 👍👍

[Swizzletwice]

Interesting video and explanation of data compression techniques.

[chrism]

Interesting, thanks! I never really noticed this

[-]

Thanks for the video. I was mystified why, when combining CSV files, where the primary column is consecutive days, or date and time at set intervals, the rows become jumbled up when loaded into Excel.

[jasongins]

I've seen this with the data model, but never knew the nuts and bolts as to why. I won't get mad at it anymore. How did you figure out that it is a memory management strategy by way of the various encodings?

[williamarthur]

'Now you understand' yeah right, this is going to take just a few more re runs.