Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Confused about joins

I'm used to writing SQL queries, but new to Power Query.   I'm struggling a bit to break out of my habbits.   I have a selection of one table (T1), that results in a few hundred lines.   I need...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

smpa01's avatar
smpa01
Community Champion
4 years ago

Anonymous if the tables already exists on SQL, do the server-side transformayion and bring the transformed table to PQ. In my experience, no query folding has ever matched the server side transformation performance.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I completely agree with this. However, in most cases I'm not responsible for the DB's I'm querying and getting anything done server side is usually an up hill battle, hence wanting to solve as much as possible with queries.

 

I know this is not the perfect situation, but we work with what we have, right? 🙂

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous just being curious. If you are fetching the tables from SQL that means you have READ access which also enables you to write SQL queries, that does not affect the original tables structures in SQL and it also does not require involving a DBA of the SQL.  I am probably missing something but I am trying to understand what prevents you from writing the SQL queries at this point.  

    select a.colA,b.colC from t1 a
left join t2 b on a.colA=b.colA
left join .........

     

    • KNP's avatar
      KNP
      Super User
      4 years ago

      smpa01 - I think based on the original question and subsequent reply (here), Anonymous was following 'best practice' to let PQ build the query to ensure folding rather than a native query. 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ah, sorry, I was somewhere else in my head and misread your suggestion. I thought you meant set up views server side, which I've seen suggested before.

       

      I don't mind at all to build the SQL queries, but as initially stated I've gotten the impression that this approach is frowned upon.

       

      I kind of see why, as well, since the people I tend to collaborate with do not typically know SQL as well as they know PQ.