Binding text filter to parameter

Question

Hi,I have a text filter which is linked to parameter using "advanced filtering"&nbsp; "is" "ParametersDOCNO". The original value for parameter DOCNO is "" I my SQL query&nbsp; I wrote "and CAST(D.BELNR AS STRING) LIKE ('" &amp; DOCNO &amp; "') ", but I don't know why, value of text filetr is no passed to parameter DOCNO. Does anybody know how to solve it?&nbsp;Thanks

toddchitt · Answer

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.&nbsp;
&nbsp;
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:
&nbsp;
AND B.BELNR LIKE '%' &amp; DOCNO &amp; '%'
(or possibly AND B.BELNR LIKE '%' + DOCNO + '%'
&nbsp;
Suggestion: get it to work inside SQL, THEN transfer it to Power Query

alfive · Answer

Hi&nbsp;This is my SQL. If I would replace the DOCNO by a number, it would run ok.

toddchitt · Answer

What is your source system? I am guessing it is NOT SQL Server.
&nbsp;
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?
&nbsp;
Edit the main query to have a statement like this: [Query = MyQueryText] so that it 'runs' the query text from MyQueryText.

alfive · Answer

Hi&nbsp;I'm using SQL Dtabricks

toddchitt · Answer

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.&nbsp;
For example, does the sample following syntax work:
WHERE CAST(D.BELNR AS STRING) LIKE ('ABC123')&nbsp;
?
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.

Forum Discussion

Binding text filter to parameter

8 Replies

Related content

Binding Date parameters

No binding to Parameter

Facing one issue while binding M Query parameter in Slicer

Parameter Binding

Unsupported Filter Error with Dynamic M Query Parameter Binding on Visual Selection