Wow! I always thought query folding was some sort of witchcraft that was too complex for my meager brain to understand. But once again, your video cut through the fog of confusion and made it relatively simple to grasp. Thanks so much! PS - Yes I have a lot of dataflows that take many minutes to run - I will definitely go do some inspections, now.
Interesting video, many thanks! In my job, all our Power Queries take their data from Excel sources, so folding doesn't occur. This concept is useful to know, however, just in case things change.
I also don't query external databases but it was so well explained that I feel I have a much better understanding of how PQ works "under the bonnet". Thanks!!
@bcti-bcti I have a list of IDs I want to pass dynamically back to a query being folded. Is this possible, i.e., use a particular step as part of SQL WHERE IDs IN (IDs to be inserted) Statement
Finally someone gave nore details about query folding. Thank you for that. I got a question, and I hope someone can give me more details. You clearly mebtioned about the folsing happening when the step can be translated to SQL, is there a reference documentation that expand on this concept? Are there any clauses that won't work with folding?
I don't know of any official (or unofficial for that matter) documentation that lists foldable versus non-fordable actions. This would vary wildly from data source to data source. As far as SQL-specific clauses, I would say that if it can be done by writing the SQL statement in any other fashion, then it's most likely a foldable step. Thanks for watching.
Just wanted to revisit this video. I wonder whether it make any difference if one, instead of relaying on the folding function, simply sets the SQL query at the source dialogue. I have done it many times in the past to get more organized and overall less data to deal with on the power query side. Writing your own query disables the folding, as far as I remember, but at the time I felt like the native SQL query would take noticeably less time to run and refresh. Did anyone do any test comparing (properly working!) folding vs native SQL at the source? Thanks
Note: Just because you lose the folding indicator, you may still be folding. There is a tool that you can use to see the actual query that is being generated to KNOW for sure.
This is such a great explanation. I usually describe it as “outsourcing” back to the source. One thing I found interesting - you did a Changed Type step that didn’t break query folding. Usually they do.
@@bcti-bcti I think of it more like a step that the PQ engine knows how to translate to SQL. ALL datatype conversions are possible in SQL, it's PQ engine that is not translating it correctly.
It's always good to be informed about anything in PQ; I don't deal or use SQL, but who knows! Query Folding. Thanks for the info. (I haven't watched the vid yet😊.)
Thanks so much for this video, I'd like to see the whole setup though, from the connection to the server through to the end result. Only to see if the steps I'd normally take in Power Query are the same or different if it's being actioned at the server level. Really appreciate these vids.. :)
I think some steps are folded to Access, but it seems to break far more easily than when using a more sophisticated database like SQL. Thanks for watching.
Wow! I always thought query folding was some sort of witchcraft that was too complex for my meager brain to understand. But once again, your video cut through the fog of confusion and made it relatively simple to grasp.
Thanks so much!
PS - Yes I have a lot of dataflows that take many minutes to run - I will definitely go do some inspections, now.
Let us know if you are able to get any of them to process faster. Thanks for all your support.
Interesting video, many thanks! In my job, all our Power Queries take their data from Excel sources, so folding doesn't occur. This concept is useful to know, however, just in case things change.
Yeah, if all your data comes from Excel files, then there's not much to capitalize on here. But like you said, that could change. Thanks for watching.
I also don't query external databases but it was so well explained that I feel I have a much better understanding of how PQ works "under the bonnet". Thanks!!
Wow I definitely learnt something watching this video. Hopefully, these icons come to the desktop version
@@Simon-vc1wk I hope so as well. They’re super useful (and pretty).
@bcti-bcti I have a list of IDs I want to pass dynamically back to a query being folded. Is this possible, i.e., use a particular step as part of SQL WHERE IDs IN (IDs to be inserted) Statement
Finally someone gave nore details about query folding. Thank you for that. I got a question, and I hope someone can give me more details. You clearly mebtioned about the folsing happening when the step can be translated to SQL, is there a reference documentation that expand on this concept? Are there any clauses that won't work with folding?
I don't know of any official (or unofficial for that matter) documentation that lists foldable versus non-fordable actions. This would vary wildly from data source to data source. As far as SQL-specific clauses, I would say that if it can be done by writing the SQL statement in any other fashion, then it's most likely a foldable step. Thanks for watching.
Just wanted to revisit this video. I wonder whether it make any difference if one, instead of relaying on the folding function, simply sets the SQL query at the source dialogue. I have done it many times in the past to get more organized and overall less data to deal with on the power query side. Writing your own query disables the folding, as far as I remember, but at the time I felt like the native SQL query would take noticeably less time to run and refresh. Did anyone do any test comparing (properly working!) folding vs native SQL at the source? Thanks
@@kkravch I run some tests in the next few days and let you know my findings.
@@bcti-bcti You CAN enable folding of Native Queries. There are a number of videos and Blog posts that cover it.
@@pabeader1941 I’ll go back and check my sources on that. Either I was misinformed or I misunderstood. Thanks.
Note: Just because you lose the folding indicator, you may still be folding. There is a tool that you can use to see the actual query that is being generated to KNOW for sure.
Good to know. Can you share the name of the tool? I'd love to try it out.
This is such a great explanation. I usually describe it as “outsourcing” back to the source. One thing I found interesting - you did a Changed Type step that didn’t break query folding. Usually they do.
"Outsourcing"... I like that!. Yeah, that was a lucky one in that it was a data typing step that SQL understands. Thanks for watching.
@@bcti-bcti I think of it more like a step that the PQ engine knows how to translate to SQL. ALL datatype conversions are possible in SQL, it's PQ engine that is not translating it correctly.
@@pabeader1941 I’ll concede that point as I’m sure you are more knowledgeable about SQL than I. Thanks.
It's always good to be informed about anything in PQ; I don't deal or use SQL, but who knows! Query Folding. Thanks for the info. (I haven't watched the vid yet😊.)
Wow; a comment made BEFORE the video is watched. That shows pure, undiluted support. Thank you.
Thanks so much for this video, I'd like to see the whole setup though, from the connection to the server through to the end result. Only to see if the steps I'd normally take in Power Query are the same or different if it's being actioned at the server level.
Really appreciate these vids.. :)
To test performance, you could always break the query at the earlier stage of processing and make sure it remains broken. Thanks for watching.
Amazing tutorial. You rock!!! Thank you.
So glad you liked it. Hope it helps.
Thanks for the great explanation 👍
You are very welcome. Thanks for taking the time to watch.
Excellent as always❤
Thank YOU!!!!!
Awesome
Glad you found it helpful.
AWESOME!
Thank you! We appreciate you taking the time to watch.
Will it work with MS Access files as source? Or that doesn't matter if it's Excel or Access files?
I think some steps are folded to Access, but it seems to break far more easily than when using a more sophisticated database like SQL. Thanks for watching.
PROFESSOR!
STUDENT!!!!
(Thanks for watching.)
Thanks a lottttttttttt
You are most welcome!!!
Think SQL. 👍
Absolutely! Thanks SQL for the help.
I always think of the Fleetwood Mac song “Never Break the Chain”-never break the fold!
Now I'll be hearing that in my head from now on. Thanks for the earworm.
Five stars.
WOW! Who could ask for anything more? Thank you.
I guess getting data from Microsoft access won't be speeded up either
I think some steps are folded to Access, but it seems to break far more easily than when using a more sophisticated database like SQL.