Forum Discussion
Binding text filter to parameter
There are a couple of key elements that need to be in play for this to work.
1) The table need to be Direct Query. Import Mode doesn't work.
2) The Power Query parameter needs to be selected in the Advanced part of the Model view.
It sounds like you have alreay gotten this far.
3) The parameter value needs to be injected into the query.
Can you show us a screen shot of your query? I don't think SQL supports the syntax you have. a) you cannot CAST AS STRING, you need to CAST (xxx AS VARCHAR(100) ) or AS NVARCHAR(100) . B) The % sign is the usual T-SQL place holder for any number of charachters, so if you want to do a LIKE comparison and inject the DOCNO paramete value, the syntax needs to be something like this:
AND B.BELNR LIKE '%' & DOCNO & '%'
(or possibly AND B.BELNR LIKE '%' + DOCNO + '%'
Suggestion: get it to work inside SQL, THEN transfer it to Power Query
Hi
This is my SQL. If I would replace the DOCNO by a number, it would run ok.
What is your source system? I am guessing it is NOT SQL Server.
Maybe try this:
In Power Query, create a BLANK query, (let's call it "MyQueryText") then use the Advanced Editor to set it to the big long query you have above. Enclose the whole thing in quotes. Click on the query and read the result (NOT in Advanced editor). Does it made sense when you enter values for your parameters?
Edit the main query to have a statement like this: [Query = MyQueryText] so that it 'runs' the query text from MyQueryText.
Hi
I'm using SQL Dtabricks
Try the trick of separaging the actual query text, with its embedded parameters, from the query that connects to DataBricks and RUNS that query. This is a great way to review the results of the query text itself to see if it makes sense, correct syntax, correct placement of single and double quotes, etc.
For example, does the sample following syntax work:
WHERE CAST(D.BELNR AS STRING) LIKE ('ABC123')
?
If you can get the query to work with literal values like above, only THEN start by replacing those literal values with respective paramters, and every time, check the output of the QueryText to make sure it has gotten the right syntax.