Search code examples
azurepowerbiazure-synapse

Importing data form SQL Server to Power BI - Can I M code from Power Query into WHERE clause of sql code

I am wondering is it possible to include the M code of Power Query in Power BI in the WHERE clause of a SQL statement, when I am getting data from Azure Synapse Analytics SQL to Power BI?

The table I am querying is in Azure Synapse Analytics and it is more than 1 million rows. So I am trying to import only the rows for specific Post codes. The problem is that these Post codes are being provided via separate Excel file which is uploaded to Power BI.

I have used the M code from the comments, but tweaked it a bit in order to put get a coma between each postcode.

= Text.Combine(MyTable[PostCode2], ",")

Then I want to use this code, which returns:

'CO16 0AS','CO10 8JR','CO1 2TA','NE15 8TN'

I want to use this in the WHERE clause in the SQL statement which I write when I "Get Data" from "Azure Synapse Analytics SQL": enter image description here

This is the code: enter image description here

And I am getting the following error: enter image description here

I have tried remove the #, the " symbols, brackets, and other variations, but nothing works.

Solution

  • Say you have a Query in PowerQuery of a table with a column of the Post Codes you are after. You would generate the string for SQL IN with the following:

    "'" & Text.Combine(#"Post Codes"[Post Code], "','") & "'"

    Where #"Post Codes" is the name of the query, and [Post Code] is the column name.

    Add the above in the Advanced Editor of your SQL import, and watch out for ". For example:

    ... "SELECT * ... FROM ... WHERE ... IN ('" & Text.Combine(#"Post Codes"[Post Code], "','") & "')"