Forum Discussion
ODBC.query with Excel cells parameters
Hi Michael,
Ross's solution is also a wonderful way.
Regarding your issues, I had uploaded a demo. Can you download it? Maybe you can see the details if you have the file. The error message shows something related to the privacy level. I just ignored it. You can set them according to your needs. Please refer to the snapshot below.
Best Regards,
Dale
Hi Anonymous,
According to my test, we should load them as connections. Please download the demo in the attachment. Though it can't work in your side, you still can see the details.
let
Source = Odbc.Query("dsn=sqlserver", "select * from twoDates where startDate >= '" & Text.From(Table14) & "' and endDate < '" & Text.From(Table15) & "';")
in
Source
Best Regards,
Dale
Dale,
thanks for your prompt reply. I tried your method but still have an error, the system says it can't convert a value of type Table to type Text.
Above (and below if clearer) is my query, with the tables I tested (startdate = datedebut, only difference is that startdate is a table, and datedebut is a table converted in string)
= Odbc.Query("dsn=IP21 64bits", "select name, ts, max, avg from aggregates where (name='4.REAC1.K+.PPM.PV') and ts between '" & Text.From(startdate) &"' and '24-OCT-18 00:00:00.0' and period=1:00 and STEPPED = 1")
I tried to replace "Text.from(startdate)" by datedebut, but still I get an error, even if as I said before the SQL chain seems the same...
Or Maybe I missed something in your explanation ?
thanks again
regards
Michael
Hi Anonymous,
Did you notice the step "Drill Down"? It will turn the table into a single value. Finally, don't click "Close & Load" directly. Click the triangle and choose "Close & Load to" to "Connection only".
Best Regards,
DaleHi Dale
yes, I noticed it and already did it. 2 cases :
when I click on drilldown, the code I get is
= #"Changed Type"[Column1]
in that case, i've got a kind of tab with the name "list" above the value. And the query refuses the operator "&" saying it doesn't work with Text and List ??
I changed the code to
= #"Changed Type"{0}[Column1]to get a single value, and in that case I've got the message
"Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
Promised, I followed all the steps, but I must do something wrong, and/or badly explain my issue. Strange thing is, in your case you have a single value but it looks like a table. When I click on Drilldown, I've got "ABC" or another picture in front of my table, and no more the picture "table", maybe you can see it on the picture of my previous post.. Don't know if it matters ?
I will try the solution from Ross today or tomorrow
thanks again !
Michael
Hi Michael,
Ross's solution is also a wonderful way.
Regarding your issues, I had uploaded a demo. Can you download it? Maybe you can see the details if you have the file. The error message shows something related to the privacy level. I just ignored it. You can set them according to your needs. Please refer to the snapshot below.
Best Regards,
Dale