Using GetValue (NamedCell) and Logical Type error

[patator35]

Hello,

I'm trying to retrieve the value of a cell in my query but end up with an error message:

The part of my query getting the error is :
= Table.SelectRows(#"Type modifié2",each GetValue("SQL"))
The (GetValue("SQL")) correctly returns "34" (value of my cell) but a message says that 34 can not be converted to a logical value :
"Nous n'avons pas pu convertir la valeur 34 en type Logical.
Détails :
Value=34
Type=Type"

I have set all source permissions to public and have activated the Fast Combine option as read in some forums
What to do ?

Thanks

Patrice

[Greg_Deckler]

Your second parameter needs to evaluate to true/false, so something like:

= Table.SelectRows(#"Type modifié2",each GetValue("SQL")=34)

[patator35]

Thanks but the contents of the names cell will vary . There will be other "or" statement in the sql.
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.

[MarcelBeug]

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"))

[patator35]

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"

[MarcelBeug]

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"

[patator35]

Thanks for the great tips. I will try them all out next week when connected again to my work network

To answer your question, the value returned by getvalue is a string, which is contained in a particular cell of sheets(1).

The content of the cell is exactly at the moment :

[area] = "31" or [area] = "32" or [area] = "33"

for another salesman using the file, it can be

[area] = "35" or [area] = "36"

I guess that one of your solution will work even if I don't like the way PQ is retrieving the data from my network source (anothre xlsx file).

From what I understood, PQ is retrieving all data available in the xlsx datasource and then use its internal engine to run the "where" clause just taking area 31, 32...

I've been looking for hours for a solution which will  only retrieve the data I need without importing first the whole database (could be too long for remote vpn access vs local network access to the company server).

I used to do it in the past using an ODBC driver and a simple sql statement : select * from mydatasource where [area] = "35" or [area] = "36"

By any chance, do you know a way to avoid the #"Filtered Rows" solution, taking place once all data have been imported.

THANKS

[MarcelBeug · Accepted solution]

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.