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:
www.bcti.com//...
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.

[@bcti-bcti]

@@Quidisi Glad you found this helpful and that the lessons are valid. Save that time for other things, like visuals refinement. Thanks for watching.

[@ahmedshalaby9343]

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

[@bcti-bcti]

Thank you (from the U.S. of A.). That is an incredibly nice thing for you to say. I'll try not to let you down in future videos. Thanks for watching and your support. Cheers.

[@kebincui]

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

[@bcti-bcti]

Thank you! I thought I was going mad for a while when witnessing this behavior. Cheers.

[@JuanCVazquez-s9k]

Awesome! Very interesting and helpful. Thanks Professor.

[@bcti-bcti]

@@JuanCVazquez-s9k Glad you found it useful. Thanks for your comment.

[@Swizzletwice]

Interesting video and explanation of data compression techniques.

[@bcti-bcti]

Thank you. I appreciate your viewership and support.

[@kkravch]

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

[@bcti-bcti]

@@kkravch Yes, that could very well be. Thanks for watching.

[@RonDavidowicz]

Excellent explanation

[@bcti-bcti]

Thanks again!!! Glad you found this helpful.

[@chrism9037]

Interesting, thanks! I never really noticed this

[@bcti-bcti]

@@chrism9037 Things that make you go “hmmm”. Thanks for watching and commenting.

[@williamarthur4801]

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

[@bcti-bcti]

@@williamarthur4801 Watch it 100 more times. It helps my view count. 🤣

[@16-7]

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.

[@bcti-bcti]

Exactly! I was puzzled for the longest time over this behavior. Thanks for watching.

[@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?

[@bcti-bcti]

@@jasongins By reading a VERY long and in-depth book named “The Definitive Guide to DAX” by Marco Russo and Alberto Ferrari. Thanks for watching.