Forum Discussion
ODBC.query with Excel cells parameters
Hi Guys !!!
I'm new in this forum and quite new with power pivot and power BI.
I've got one question that may seem easy for you guys. How do I link a Power BI parameter to an Excel cell ?
Here is my issue : I have an ODBC connection with ODBC.query, and 2 dates, start date and end date. I first created 2 tables based on Excel cells, as explained in https://accessanalytic.com.au/powerquery_namedcells_parameters/, and tried to insert these parameters in the query, but it doesn't work, I've got an error.
Then I created a parameter inside power BI, inserted it into the SQL chain, and it worked. What I would like to do now is to link this parameter to an Excel cell. Can someone tell me the way to proceed ?
thanks !
regards
Makol76
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
11 Replies
You could create a Query Function that references a table and a specific value. You could then use that function within your query that requires the parameter to run.
An example might be:
let Source = #"TestData", #"GetSpecificCell" = Source{1}[Item] in #"GetSpecificCell"Where "TestData" is the query containing the excel file and {1} refers to row 1, and [Item] is the name of the column containing the value you are looking for.
So in your case, if you had a particular sheet and table with the data you want. Your "TestData", would be a set of steps opening the Excel file, getting the specific sheet you want, removing the rows you don't need and just having your table of parameters. Your function(s) like the above just pick out the individual values.
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 SourceBest Regards,
DaleDale,
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,
Dale