cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 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.

ODBC-query-with-Excel-cells-parameters3

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-jiascu-msft
Microsoft
Microsoft

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

ODBC-query-with-Excel-cells-parameters

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Anonymous
Not applicable

@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.

Capture.JPG

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

ODBC-query-with-Excel-cells-parameters2

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

Hi @Anonymous try changing your code to:

 

#"Changed Type"{1}[Column1]

I don't think it will accept a zero.

Anonymous
Not applicable

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

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.

ODBC-query-with-Excel-cells-parameters3

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

@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
Not applicable

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors