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

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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'

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

Then I created the 'CustID'

Source = Excel.CurrentWorkbook(){[Name="CustID"]}[Content], SourceValue = Record.Field(Source{0}, "CustID"), SourceText = Number.ToText(SourceValue) 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?

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:

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)) #"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>
                got a few errors Expression.Error: The field 'Column1' of the record wasn't found. Details:     CustID=1
– user2206329
                Jul 17, 2019 at 1:38
                Expression.Error: The key didn't match any rows in the table. Details:     Key=Record     Table=Table
– user2206329
                Jul 17, 2019 at 1:39
                Hmm. If you take off the {0}[Column1] in that first line, what does the table look like (at that step, not at the end)?
– Alexis Olson
                Jul 17, 2019 at 14:25
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.