Forum Discussion
Using GetValue (NamedCell) and Logical Type error
It looks like alternative 2 would be the one you are looking for, HOWEVER:
Regarding your question if #"Filtered Rows" can be avoided: you can retrieve your data directly from the SQL database, in which case all logic may be executed on the server, which is definitely the fasted solution.
If you connect directly to the SQL database, the logic will be executed on the server in either of 3 cases:
1. Query folding is in place.
Your query must be set up in such a way that the internal engine can translate the entire code to a native query, which you can check by right clicking the last step in your query and choose "Native Query": if it is greyed out, then there is no query folding, otherwise you will see the SQL-statement that is used to retrieve the data from the server (which is a translation of you entire query definition).
It can also be that query folding is in place up to a particular step in your query.
A statement using "Expression.Evaluate" would definitely disrupt query folding.
2. Direct Query mode.
In this case, the data is retrieved from the server once required, i.e. when visuals are displayed.
3. Provide a SQL Statement (under "Advanced options") when connecting to the SQL server database.
See screenshot below.
Otherwise this is basically all I know in this area; I have no experience with it, so in case of any further questions, I will probably not be able to provide an answer.
In that case, my advice would be to start a new topic with your specific questions (well reflected in the topic subject), as a topic with 0 replies will get attention from other helpers.
Your second parameter needs to evaluate to true/false, so something like:
= Table.SelectRows(#"Type modifié2",each GetValue("SQL")=34)
I can not use your solution =34
The getvalue("sql) returns exactly " [area]="34" or [area]="36"...
If I replace the getvalue("sql") by the exact same text than what is returned by the function(" [area]="34" or [area]="36"), it works great.
It is all rather confusing: according to the error message, GetValue("SQL") just returns a number (34), not "[area] = 34".
Anyhow, I guess you need:
= Table.SelectRows(#"Type modifié2",each [area] = GetValue("SQL"))You're right, it's indeed confusing re'reading my first post. I'm sorry for the confusion trying to simplify my question.
Let's explain it clearly
Using a VBA code, I generate, in a specific cell, all the commercial areas followed by the user opening the file (using the username of the PC and a table matching usernames and areas).
Let's say the user is X, he will be able to retrieve all data (from a file located on a server) 'where [area] = "31" or [area] = "32" or [area] = "yyyy" '
In PQ, if I manually write
= Table.SelectRows(#"Type modifié2",each [area] = "31" or [area] = "32" or [area] = "yyyy")
everything works great
The content (generated by vba code and will vary depending on the salesman) of the "SQL" range cell contains :
[area] = "31" or [area] = "32" or [area] = "yyyy"
If I write
= Table.SelectRows(#"Type modifié2",each GetValue("SQL"))
which should be equivalent to (in this example)
= Table.SelectRows(#"Type modifié2",each [area] = "31" or [area] = "32" or [area] = "yyyy")
Then I end up with the error message :
each [area] = "31" or [area] = "32" or [ar...can not be converted to a logical value :
Détails :
Value= [area] = "31" or [area] = "32" or [area] = "yyyy"
Type=Type"Thanks for your explanation.
However, I'm still struggling to replicate your situation, specifically what exactly is returned by function GetValue (apparently it is not a string nor a function, so what woudld it be? I don't know).
I created 2 working alternatives, hopefully this provides the clue to solve your issue.
Alternative 1:
Function GetValue1 returns a function. Notice the "each " is included in the function.
(String as text) as function => each [area] = "31" or [area] = "32" or [area] = "yyyy"
Query1:
let #"Type Modifié2" = #table(type table[area = text],{{"30"},{"31"},{"32"},{"yyyy"}}), #"Filtered Rows" = Table.SelectRows(#"Type Modifié2", GetValue1("SQL")) in #"Filtered Rows"Alternative 2:
Function GetValue2 returns a string:
(String as text) as text => "[area] = ""31"" or [area] = ""32"" or [area] = ""yyyy"""
Query2, using Expression.Evaluate to execute the string expression, prefixed by "each " in the query:
let #"Type Modifié2" = #table(type table[area = text],{{"30"},{"31"},{"32"},{"yyyy"}}), #"Filtered Rows" = Table.SelectRows(#"Type Modifié2", Expression.Evaluate("each " & GetValue2("SQL"))) in #"Filtered Rows"