ODBC.query with Excel cells parameters

[Anonymous]

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

[v-jiascu-msft]

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

[Anonymous]

I tried a lot of different google results but this '" & Text.From(Table14) & "' piece was what got me through my difficulty.

[Anonymous]

@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

[v-jiascu-msft]

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

[Anonymous]

Hi 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

[Anonymous]

Hi @Anonymous try changing your code to:

#"Changed Type"{1}[Column1]

I don't think it will accept a zero.

[Anonymous]

Hi guys

it works now, you are awesome !!! It was this matter of privacy, I changed the settings, et voilà !

@Anonymous,

0 works. As in many programming languages, first item in tabs is numbered 0. If I put 1, I've got an error saying I don't have enough elements in my table. But thanks for helping me, I appreciate !

@v-jiascu-msft

thanks again for your patience and your clear-as-crystal explanations !!

regards !

Michael

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

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]

@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

[Anonymous]

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