As question states:

Within powerBi there from the 'Get Data from SQL Server' -> connecting to the SQL Server

there are two options import and advanced. With Advanced, you can write a sql query to get the data or the default is import. This shows all the tables on the server and you can just ETL from a click.

What is the real difference?

CodePudding user response：

If you are comfortable writing your own T-SQL select statement, you can use it to bypass the Power Query editor and send your desired statement straight to the SQL database. That is also handy if you have code already written out from a previous query or project, which you can just paste into the Advanced query window.

If you use the Power Query Editor to build you query step by step, you have a better visualisation about what data is returned by the previous step(s), and you can apply data manipulations after sighting the data.

Power Query uses query folding, which means that your individual steps are analysed and then translated into the most efficient SQL code before it is sent to the server.

That means that even if you don't speak T-SQL very well, you can still build efficient queries with the Query Editor, and if you feel you are an accomplished T-SQL developer, you can shortcut the Query Editor steps altogether. Of course that means that it is also possible to use "Advanced" and write clunky, inefficient T-SQL that performs slower than going through the Query Editor steps would.

In the end, it comes down to preference and familiarity. A seasoned DBA might just quickly write out a Select statement, a SQL rookie might prefer to click a few ribbon commands instead. The result can be identical in returned data and performance.