Enable QUERY FOLDING for native queries in Power Query / Power BI

[@scottpowell9985]

Wow this is huge! And gotta say frustrating, this should be the default behavior! Thanks for this great info!

[@npaler01]

What!? I think Alex actually told me about this during a demo he gave at work a few months ago and it didn't click until now. Awesome!!

[@kasperc7851]

I like the way how you present and be your true self in the videos

[@shawnweccele5713]

By far probably one of your most useful videos yet! Saved me from needing IT to create about 50 Views for me. You mentioned possible exceptions and I saw it in the form of a Native Query that has a Common Table Expression (CTE) within it. Your method will get an error if the Native Query has a CTE, but it was a simple work around to create 2 queries, one for your main query, removing all references to the CTE, and the other for the CTE itself, and then within Power Query use Merge Queries to force whatever JOIN the original query had as well as pull in any columns. Now if someone can only discover a secret technique to Query Fold with a Redshift connection. 🙏

[@bhargavgirish4143]

Hi, this is amazing!! Like to add few points here which I felt could probably help. If you look at 03:14, query folding creates a new query using the native query as subquery. This could help you out by testing native queries over in source and also know the performance it might take when folding all steps on top the base query. This will allow you to make the most optimized query, plus fold on top of it using query folding would increase the performance even more!

[@shaunandkatewilkinson1746]

Unbelievable - The work Ive had to do to get around the folding disappearing. Thanks for the post !

[@anshumaannalluri456]

This is so amazing! FYI I am learning Power BI to upskill myself and your videos help me a tonnes!!! Thank you!!

[@netflixaccount3210]

Never knew, always assumed that Native Queries broke query folding because of the ugly shade of grey. Thank you so much for posting this!

[@Milhouse77BS]

Good timing. Was just looking at Webb's post from Feb

[@vamsikrishna254]

This is crazy tip ..............You saved my whole work :) Thank you so much.

[@Mehendi_boutique96]

This definitely deserves a thumbs up😎. Can be a major step for huge reports. Thanks Genius 🤘🏻

[@MeVe-t7x]

Hey, in minute 2:05, when writing the formula what is pbi_internetsales? With what should I replace it in my formula?

[@Kats072307]

same question here

[@herrsan]

can someone please explain to me why PowerQuery did not identify the filter in 2:39 to be compatible with Native Query? I would have expected this step to have a View Native Query not to be greyed out as it clearly is "translatable" into SQL. Can anyone please explain this to me (I am rather new to Query Folding)?

[@blairthomastoews]

This is great! However, after playing around with this, it looks like when using CTE's or temp tables in the native query, the "null,[EnableFolding=true]" syntax doesn't work. Any idea why that would be? Thanks!

[@Guiburgueir4]

Directly from Microsoft Learning Resources Module "Get Data" Section 8: "When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you are not pulling data from stored procedures or common table expressions (CTEs)." They've mentioned it works when using VIEWs in an earlier section though.

[@RetroSpectrum7]

This is a huge deal! What great integration with SQL. Can't wait to see how much this improves performance!

[@jaymalabarot3989]

Hi Patrick, Thank you so much for this video, this is exactly what I was looking for. But when I tried using the parameter [Enable folding=true] it gives me an error saying 'EnableFolding' isn't a valid option for this function. Can you please help? Thanks

[@zeisslaimen1]

When I tried to do the folding, I got error "DataSource.Error: We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option." Any idea?

[@martymcfly00333]

Hey Patrick, I tried this myself. I'm using PBI Report Server Jan 2021 version. I followed the steps exactly and I got an error that says: "DataSource.Error: We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option." My source is Sql.Databases("Server"). My native query is a little complex brining in multiple sources in order to normalize it in a way that would make sense with other data sources. Any advice? Also, unlike yours the "View Native Query" option is turned off from Source and beyond, It seems to never have turned on in the first place.

[@guillermobarrachinajornet1150]

Is it possible to use it in a Dataflow?

[@rahulgouda1656]

Hi, When I tried the same thing on the PowerBI desktop, it throws an error "Native queries are not supported by this value". My data source is Mongo DB, connected to power bi through ODBC connector for BI. As per the Microsoft documentation, the data sources for the ODBC connector support query folding. I am struggling a lot with this as my incremental refresh is very slow. Help from anyone is appreciated. Thanks!

[@jordanchapel4514]

Nice! I am about to use this trick right now! Thanks!

[@alexandrudades]

Thank you for all the amazing work you are doing! This information actually helps a lot!

[@helloworld9034]

Wow I gonna tested on a task that I been struggling Thanx

[@TheSiNnEr734]

Hey Patrick. Can you make a video on how to build that refresh status dashboard where you checked the seconds to refresh

[@lingqinghao3681]

Thanks for your great video! Just one question: why is number "1" automatically added to the end of the database name?

[@arayow]

Thank you for this, Adam. I am having a weird issue though - I can refresh my dataset in my desktop without any problem but I am receiving this "We cannot fold on top of this native query. Please modify the native query or remove the 'EnableFolding' option."" error when I am refreshing the same published dataset in the service.

[@lifeboston853]

Hi Patrick, this is great video! Thank you! Question: how to build this job trace reporting report? Where to get incremental refresh timing data?

[@MegaGauchiste]

Yo, did you find how to get it ? need this tooo

[@denpries]

Unfortunately it does not seem to work with Simba Athena? Expression.Error: Native queries aren't supported by this value

[@EMB3D]

how can you put a dynamic query into the Value.NativeQuery(TestPcd1, "SELECT * FROM " & my_schema & ".MyFact", SchemaName, [EnableFolding=true]) ... it gives me error that folding is not supported in my query, but if i remove the & my_schema & part, it works. So how can one add a dynamic query here?

[@4lfatir]

do we have an update whether it really speed up all your refreshes?

[@krishnaveen84]

Hi, I just want to how to do the below task in power BI. Task: Team member can see his only performance in page at the same time he has to see his Team Performance as well in the same page. How can I do this using RLS. Please share your ideas!. Thanks in advance 👍

[@erikaahlander9550]

Hi, I tried this on my query, but I only get this result "Expression.Error: 'EnableFolding' isn't a valid option for this function. Valid options are: (none)" is that something new or something in my setup not allowing for it?

[@eljangoolak]

Hi, how would this work with Azure Databricks source?

[@vkkpp]

Hi , I tried the the same step for oracle DB source the but I am getting an error as soon as I add the property [EnableFolding=true]

[@ZachRenwickData]

I’ve also been telling people that query folding breaks when using native SQL queries. In this case, it’s nice to be proven wrong! 😂

[@AgulloBernat]

But the use case is very narrow, isn't it? It's people who got a native query from somebody, but don't want to bother to modify it to suit their needs so they just use powerquery, right? Because it will only fold if you do stuff which can fold of course. Maybe I'm wrong, but that's what I thought

[@saifjamadar9703]

Can we enable the query folding if my source is Azure Databricks?

[@MahmoudSamir7oda]

Thank you i have been looking for that in the two month or so. I like your t-shirt how can i get one the same with Bring data to life

[@MrSparkefrostie]

Hmmm, Value.NativeQuery(FinalStep, "insert into SQL_Table) ???? Thinking if this is the easiest way to strip content off of a pdf to drop into a sql db

[@lancesmith1078]

Thanks man!!! This changed my life!

[@ashdam]

I have tried with Vertica database and it doesnt work. Do you know a workaround? NativeQuery is only for SQL and Oracle. Any documentation that says supported systems vs power query M senteces compatibility?

[@benzenjoseph5310]

I used the Enable Folding option,It is working well in Power Bi Desktop but when i publish it to service and refresh it throws an error saying Enable Folding isn't a valid function

[@nareshnaidu4557]

Does this trick works for ODBC(Snowflake) data source

[@pritipatil4516]

very useful thank you!😊

[@louism.4980]

This is awesome, thank you for the demo! However, I thought query folding happens automatically by the engine as part of the optimisation process?

[@ivavucak9863]

Hi when you type "select * from pbi_internetsales" - what is pbi_internetsales exactly? I'm lost as to what to put here. I'm connecting to Odata. Thanks in advance!

[@GuyInACube]

pbi_InternetSales is the table/view in SQL Server. This approach doesn't work with OData i believe. The datasource used in the video was Azure SQL Database.

[@maniknag6468]

Dear Patrick, how can I select different columns from different Database table and make a consolidated table in power BI

[@MasterCamus]

What's the difference between connecting this way and then writing a native query when compared to writing a SQL query when connecting to the data source?

[@benbill8739]

Hi guys, I'm using an Azure SQL DB with Azure AD. I can connect to this using SSMS and SSDT but when I try to connect with Power BI Desktop it won't authenticate the Azure Active Directory account (EXTERNAL_USER rôle). If I use an SQL login (SQL_USER rôle) with Power BI it works fine. Any thoughts on what I need to do to get this up and running? I now use ODBC connexion but this is not optimal and impossible to refresh data via Power BI Portal Thanks for any help

[@garavena]

I'm using an oData source and when I try to just use an expression such as Value.NativeQuery(Categories_table, "select * from Categories_table") I get Expression.Error: Native queries aren't supported by this value. Details: [Table] Anyone have any idea why?

[@nicolasbelleville5594]

This is sick! Thank you so much.

[@Kallav_World]

It's cool...does it work with Impala connector as well? Getting error - native queries aren't supposed by this value.

[@eleftheriakoniari3392]

I tried it but didn't work. MS SQL Server is in the list of sources who allow query folding? I used a native SQL query on a View (SELECT bla bla bla FROM blaView) does matter if the query is in a table or a view? Also I have only read rights to the MS SQL Server. If the folding doesn't work I have to ask from IT to write Queries and that's a problem...at the moment I get time out very often.

[@pthapa55]

Hi, I am trying to do query folding on stored procedure however it is giving me error on the steps. 'Expression Error: We cannot convert the value null to type Text' Can you please let me know, why? Thank you. Here is my code: = Value.NativeQuery( Sql.Database("ServerName", "SampleDatabase", [Query="Exec Sample_Stored_Procedure"]) ,null,[EnableFolding=true])

[@yukithejapanese]

This is bananas!

[@Guiburgueir4]

Shouldn't we create native queries manually and do the job of query folding ourselves? What is the downside apart of weakening access and data security?

[@dozaarchives2225]

This is so easy it's silly. All these "hidden" MS features. Why not have it in the Query Folding master doc?!?

[@inaciogoes6317]

I got this error Expression.Error: 'EnableFolding' is not a valid expression for this function. the valids options are :(None)

[@somnathchatterjee2192]

Hi Patrick, I just have one question regarding Query Folding, You shown that if we are using query folding then query refresh will take very less time and it will improve the perfornance. So, you shown to write a custom M code for enable query folding, I just want to know where I need to add this step? At the end of my final applied step?

[@bobkiggundu9010]

This is BANANAS!!!!...Thx Patrick

[@sujitkadam8386]

I had known about this but I wish this is possible for incremental refreshes as well. I don’t think so it works for incremental yet unless I am wrong.

[@blakeanthony3050]

Doesn't seem to enable incremental refresh for me either, all refreshes take about the same amount of time. I think my queries are too complex because even when I add a simple step after (like sort by descending), that step doesn't show the native query.

[@jordaneatsbacon]

Hey Patrick! Is anything like this possible using dataflows in a Premium workspace?

[@cognovo]

Hi Patrick, I am so grateful for your great show! By the way how do you manage to zoom in and out on different menu items to show them in a focused way. How are you doing that? It’s like magic!!

[@cbovikings]

kzbin.info/www/bejne/m56uhIB8d9qmnK8

[@cognovo]

Thanks for the link. But I do not mean Zoomit. For example in this video Patrick opens the Navigator window and then resizes it while setting the background to transparency. How is he doing it? On top he is marking an item with a rectangle and you can watch him painting the line. How are the guys doing that?

is it working with Power query in Excel?

[@shwetkumar1719]

let Source = Odbc.DataSource("dsn=TestDWH_Appointment", [HierarchicalNavigation=true]), DATA_Schema = Source{[Name="DATA",Kind="Schema"]}[Data], result_View = DATA_Schema{[Name="result",Kind="View"]}[Data], #"Filtered Rows" = Table.SelectRows(result_View, each [appointmentDate] >= RangeStart and [appointmentDate]

[@damionc]

This is huge. I am on it right now.

[@denpries]

Hi Patrick! Cool stuff. Can you tell me something on the job trace reporting?

[@Prashantpatil-cq3yt]

Sir please ask subscribers for their problem and scenarios which they are facing when developing reports... So most of them will tell you about their problems and scenarios so you can make videos on solving that problem... So more the content, more the comments and more the views as well subscribers.... Because you have much expertise in DAX... Thanks I mean to start doubt clearing video series... And put it the pbix file along with that video... So it will also useful

[@niranjanmakkuva4639]

This is amazing. I have a question Can we refresh query based on Parameter, Say i have to pull data from 10 different sources , but few sources are monthly, few are weekly and few are daily. So i want to customize the extracts , just pull what is required for daily refresh and ignore other and similarly for weekly i should pull weekly and daily extract?

[@marlucaplus]

I have seen it with Chris and it's amazing. The query folding content is now a lot cleaner than years ago. The next step would be knowing which sources can query fold. That is always confusing. Can I fold snowflake or Redshift? I think sometimes that only SQL server will do it.

[@hendrik-stack22]

you can fold snowflake db with this

[@AbouAli01006300091]

Super helpful!!

[@NilHunter]

What's query folding? I didn't get it.

[@subanark]

Nifty, cool that it works with SQL. I thought Azure Data Explorer (Kusto) was the only one that supported this. Now if only SQL supported this in the middle of the query, like Kusto, I could be happy.

[@diogobueno5888]

Thanks a lot for this video !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@luisalejandrorodriguezcamp9516]

Oh my God! I did not now, but this is bananas

[@annepriyad5705]

Thank you, it worked...

[@ajmorgan591]

Umm, why wouldn't this be enabled by default? What are the (negative) implications of enabling it? I assume there must be some else surely this would be default behavior.

[@aaronwatsky7741]

It's a shame it doesn't work for ODBC or OLE DB 😕.

[@ZappPSR]

Thanks a bunch!

[@dbszepesi]

Nice! And....Fx adds a new step?????

[@diegobessao]

Thank you!!!!

[@SofiaMartino-h5l]

I would be the happiest person ever if this could work with REST API incremental refresh

[@damiancz2339]

Awesome!!

[@StyrofoamTumor]

David! Just take that keyboatd and FOLD it!

[@joaoluismartins]

That should definitely be the default behavior!

[@aaronwatsky7741]

It's a shame that it doesn't work for ODBC or OLE DB 😕.

[@hplaptop08]

Fixed it; I got the same error that many of you were getting this template worked for me... let Source = Sql.Databases("SERVERNAME"), CallDB = Source{[Name="DATABASENAME"]}[Data], UseNativeQuery = Value.NativeQuery(CallDB,"SQL STATEMENT",null,[EnableFolding=true]) in UseNativeQuery

[@Bristerireland]

Say whaaawat! Love it 🙏

[@mcnater]

Dang...I see this is just for SQL Server now and not Oracle. So close to being a life saver.

[@ДимаРогачёв-д6е]

Спасибо! Интересная фича, так можно сократить количество объектов в бд, написанных специально для PBI. Главное чтобы можно было подцепиться к materialized view.

[@biswajeetswaro7831]

Awesome, this is banana!!!

[@nick1334]

Doesn't work. They must have ms must have seen this and said "nope"

[@sue_bayes]

No way!!!

[@jayong2370]

!!

[@eta2001carl]

joooooooooooooooooooo

[@Shawn-cr8ep]

🤯 🍌🍌🍌 🥳 this made my week!