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
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.
Anonymous
thanks for your prompt reply. Not sure I understood your solution, sorry. I gave a little bit more of explanations to Dale, with an example of my current query, if it helps understand my issue.
thanks again
regards
Michael
What you need to do is extract a value from the table, rather than pass the table itself. In the code that I provided, there is a specific line that says to get a value from a particular column and row. See if you can use that code to get the value you need to pass in as your StartDate