Create powerBI columns with SQL queries

[andreazambon]

Hello there. 

In PowerQuery I use to build columns with M code and, if I need it, merging tables. My tables usually are excel tables.

This way is quite a trash. I would like to be more efficient building columns with SQL queryes, something like:

"Select TARGET from TABLE 1

where Value >ValueMin and Value <=ValueMax".

Sorry if my example is not syntactically correct, I need to resume my SQL knowledge :P. 

I hope that you can understand my question.

Is that possible?

Thanks.

[j_ocean · Accepted solution]

If your source is SQL it automatically converts as much as it can to SQL and pushes the work up to the SQL server.

You can manually write out SQL when you establish the connection, this is *usually* meant to copy-paste in an existing extract vs writing new SQL but nothing is stopping you writing new code there.

If your source is excel files you're out of luck. There's no SQL engine for it to send your code to.

[v-stephen-msft · Accepted solution]

Hi @andreazambon ,

In Power Query, only M code can be used.

However, you can use SQL statements to pre-filter when connecting to SQL Server.

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

Some ways about filtering in Power Query for your reference:

Filter data (Power Query) - Microsoft Support

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[abdulalim-da]

Currently, there is no direct option to use SQL queries in power query (m-code). What you can do, you can learn Power Query from here.

There is another alternative solution to using SQL queries. For that you must restore or insert Excel data into an SQL server database then you can get this data from the SQL server connector. You just need to pass the query within the advanced option field.

Did my answer help you or lead you to a solution? If so, please consider marking my response as a solution and giving kudos 👍

Thank you!

[j_ocean · Accepted solution]

If your source is SQL it automatically converts as much as it can to SQL and pushes the work up to the SQL server.

You can manually write out SQL when you establish the connection, this is *usually* meant to copy-paste in an existing extract vs writing new SQL but nothing is stopping you writing new code there.

If your source is excel files you're out of luck. There's no SQL engine for it to send your code to.