SQL vs Power SQL joining of tables

[masplin]

I have  inherited a Power BI model built by a guy who came form an SQL background whereas I come from an Excel background and very rudimenatary knowledge of SQL. 

I need to expand on some of the queries to bring in a dditionla data form related tables that was not in the original specification.  The SQL is already looknig liek spaghetti as the database has seperate data tables for each client so to create a company level query each subquery is repeated multiple times for each client database.

The question this.  I can see clearly how to write a similar SQL multi database query to extract and join all the data for each client from the additional table. I can then eassily merge this new table with the exisitng table to add the extra columns I need.  to do this in SQL is going to be alot more painful as i don't know how to edit the query. However is their a substantial speed advantage to joining all the tables together in a single SQL query rather thna using Power Query to merge the reuslts of 2 SQL queries?  If there is I better learn some SQL :). we are tlaking about 500,000 rows of data. 

Thanks for any advice

Mike

[houston39]

Hi Mike,

Do you have access to SSMS?  Also, retrieving the SQL embedded within power BI is not very difficult, one you step through it.

Check this out:

https://community.powerbi.com/t5/Desktop/Edit-Existing-SQL/td-p/48607

Then, see if you can run the query within SSMS - if you can I will assist you further.

G

[Anonymous]

@masplin,

I always recommend people to write SQL if they can to get the data, since all the heavylifting work is done at the server level, hence faster retrieving time. In some cases, depending on the types of the SQL databases, drivers, and the complexity of the transformations in Query Editor, some of the transformations might not be converted into SQL statements to run at the database server but rather be performed using your local machine's memory and processor. You can check if your transformation is converted to SQL by right click on a particular step. If 'View Native Query' is greyed out, it means the transformation is done locally, otherwise you should see the converted SQL statement. Hope this helps!