Binding text filter to parameter

[alfive]

Hi,

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

Thanks

[v-csrikanth]

Hi @alfive 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @alfive 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Cheri Srikanth

[v-csrikanth]

Hi @alfive 
As highlighted by @ToddChitt , the proposed approach appears to effectively address your requirements. Could you please confirm if your issue has been resolved?
If you are still facing any challenges, kindly provide further details, and we will be happy to assist you.

[ToddChitt]

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

[alfive]

Hi

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

[ToddChitt]

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.

[alfive]

Hi

I'm using SQL Dtabricks

[ToddChitt]

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.