Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
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
I tried a lot of different google results but this '" & Text.From(Table14) & "' piece was what got me through my difficulty.
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
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
Hi @Anonymous try changing your code to:
#"Changed Type"{1}[Column1]
I don't think it will accept a zero.
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 !
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
86 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |