how to add a where clause to a query in Power query?

Question

I need to return a set of data from a database based on a parameter that is in excel.

So I have created the following 'Query1'

let
    Source = Sql.Database("VI107064\SQLEXPRESS", "Stock", [Query="select * from customer#(lf)where custID = " &CustID])
in
    Source

Then I created the 'CustID'

let
    Source = Excel.CurrentWorkbook(){[Name="CustID"]}[Content],
    SourceValue = Record.Field(Source{0},  "CustID"),
    SourceText = Number.ToText(SourceValue)
in
    SourceText

The issue is that Excel is complaining that the query refernces other queries or steps, so it may not directly access a datasource Please rebuild this data combination.

So after reading https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

I understood the issue, and I think for me to fix the problem, I need to duplicate query1 to query2. Update query 1 so that it just returns the table. Update query 2 so that it takes query 1 as a source and then adds the filter.

Is that correct? or is there a better way to do this?

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-15T16:41:13

The simplest solution, if you aren't worried about data privacy settings, is to turn those settings off.

File > Options and settings > Query Options > Privacy

In any case, I'd recommend cleaning up the code to be a single query that looks something like this:

let
    CustID = Excel.CurrentWorkbook(){[Name="CustID"]}[Content]{0}[Column1],
    Source = Sql.Database("VI107064\SQLEXPRESS", "Stock"){[Schema="dbo",Item="customer"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Source, each ([CustID] = CustID))
in
    #"Filtered Rows"

If you right-click on the last step in the Applied Steps pane, you can see that this get interpreted in the SQL Native Query as

select [_].[ <A bunch of columns here> ],
    [_].[ <...> ],
    [_].[ <etc> ]
from [dbo].[customer] as [_]
where [_].[CustID] = <CustID value here>

how to add a where clause to a query in Power query?

1 Answers