Maintaining Query Folding when Merging with Different Data Types
HTML-код
- Опубликовано: 11 июл 2024
- Learn how to maintain your query folding when needing to merge (join) queries in Power Query, but when the merge keys are different data types, and when converting data types might initially break your query folding. Tune in to learn more!
RELATED CONTENT 🔗
Query Folding Overview -- learn.microsoft.com/en-us/pow...
BECOME A CHANNEL MEMBER 🎉
-- / @havensconsulting
CHECK OUT OUR MERCH STORE 👕
-- havens-consulting.creator-spr...
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
-- / havensconsulting
VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:30 - Content Intro
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog -- www.havensconsulting.net/blog-...
Blog Files -- www.havensconsulting.net/blog-...
Files & Templates -- www.havensconsulting.net/files...
Consulting Services -- www.havensconsulting.net/consu...
Online Courses -- www.havensconsulting.net/onli...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #powerplatform #microsoft #businessintelligence #datascience #data #dataanalytics #excel #powerapps #datavisualization #dashboard #bi #analytics #powerquery #dax #microsoftfabric #semanticmodel #queryfolding 
Наука
Excellent Reid!
Cool trick.. 👌👌Thanks @Reid 😊👍
thanks for great video
good to know!
Useful 👍
This could useful. I like my queries to fold.
Nice video. Better you could zoom in the focused frames.
Nice👏
Reid, isn't best practice is to write SQL Query instead of checking whether Query will fold or not?
Native queries have their own complexities, and by default break any ability for Power Query to fold on top of it (more below)
ruclips.net/video/DWbh7xp08l0/видео.html
It also relies on having a developer know SQL and SQL optimization techniques, which are all built into the query folding engine.
Like with most things though, it depends. :)
What is so special about query folding? Why we should care about it? Incremental refresh works without query folding. Some folks say, it is quicker. But how can "Select * from Table1" writen by me (which does not fold) be slower than same thing written by the engine?
It's the magic sauce of Power Query :). Transformations are translated from M (Power Query) to the source, and passed back for the source to transform (faster). Rather than Power BI downloading the unprocessed data, and doing it itself (slower). You can easily get a 5, 10, 20+ times performance increase when the source database does most the work. A quick google search would give you some helpful results like below on why it makes queries faster.
www.reddit.com/r/PowerBI/comments/14strbb/what_does_folding_in_query_folding_means/
Also, to note,. Incremental refresh is explicitly advised NOT to use without folding. Because without folding you download the entire data before filtering to the relative period. Which is why when folding can't be confirmed with the engine, it gives a big warning that advises not to use incremental refresh. More below on that as well.
learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
@@HavensConsulting Thank you for your explanation. Are you saying if I write "Select ProductID from Sales" myself in the Source dialog Advanced options, Power Query still loads all the data first, then executes my Select statement?
@@1yyymmmddd native queries (e.g. writing custom SQL Code in the statement box) will actually break any ability to query fold. So generally advised to not use that. It's advised to use the Power query actions to choose your columns, filter your rows, do your transformations. etc.
More info about native query limitations below
learn.microsoft.com/en-us/power-query/native-database-query