Forum Discussion
Confused about joins
Hi Anonymous ,
You're thinking too much in the moment - think end-product:
Once you've done your T1/T2 merge, you right-click on the T2 query and UNCHECK 'Enable Load'. This prevents this query having to be run when you actually refresh your report in the service, as it doesn't need to be materialised for the data model. This just leaves your merged T1 query to be run which, as you can see, has all the details it needs within its folded native query to produce your merged T1 in the model.
Pete
First
Wait, so you're saying write the query as SQL? From all I can read this is considered bad form, hence me avoiding it.
I'd much rather write an SQL query than use Power Query, but I'm doing my best to learn the correct way of doing things in PBI.
Second
Yes, I know I can filter down, but in this case that still leaves me with a lot more lines than I need. I was hoping there was a more efficient approach, similar to writing a join. Especially since my scenario involves several big tables, not just two.
Hi Anonymous ,
dgurgel 's answer is somewhat outdated, insofar as you can maintain query folding with a native SQL query by using the [EnableFolding=true] argument in the Value.NativeQuery function.
However, I agree that they are generally bad form, but usually due to transformations being 'hidden' from other devs, and the fact that not all PBI devs can read/write SQL to a high standard.
If you are using a source that is foldable, then you just bring in your T2, filter it down to only the rows/columns that you want for the merge, then right-click on the final step of the query and make sure 'View Native Query' is not greyed-out. This query is being folded back to the source.
Do the same on the T1 query step before you perform your merge to ensure folding is also complete on this query then, when you perform the merge, the most efficient SQL will be sent to the source to materialise the result.
Have a go at performing transformations/filters etc. then select 'View Native Query' at each step and you'll see what I mean.
Pete
Thanks for your reply.
This is the part that confuses me:
"the most efficient SQL will be sent to the source to materialise the result"
If I look at the native queries, as you suggest, it's clear that PBI is sending both a query for the "pure" T2 as well as for the joined T1+T2 table.
It seems to me that T2 is actually being queried twice, if I use a merge.
Maybe I'm too stuck on wanting to minimize load, but I really like to keep my queries lean and efficient.
I keep reading that PBI converts Power Query to the most effective SQL, but I don't really see that. Not with the approach I've taken, at least.
Hi Anonymous ,
You're thinking too much in the moment - think end-product:
Once you've done your T1/T2 merge, you right-click on the T2 query and UNCHECK 'Enable Load'. This prevents this query having to be run when you actually refresh your report in the service, as it doesn't need to be materialised for the data model. This just leaves your merged T1 query to be run which, as you can see, has all the details it needs within its folded native query to produce your merged T1 in the model.
Pete
BA_Pete wrote:Once you've done your T1/T2 merge, you right-click on the T2 query and UNCHECK 'Enable Load'. This prevents this query having to be run when you actually refresh your report in the service, as it doesn't need to be materialised for the data model.
There it is! That's exactly what I was missing.
Thanks so much. After doing this, my refresh is much, much lighter.