Querying SQL Server Data in Excel with a Parameter

[@byteseq]

This is not really querying with a parameter, it's filtering AFTER querying

[@davidchiles9640]

A VERY helpful video but unfortunately he is Preben is correct. I have SQL Profiled the output from this and in fact the main query is executed raw. ie. if you were to put a 3 year limit on it and the parameter was set for 1 year it would still return 3 years. then excel power query would filter that. not ideal imho. but also typical microsoft.

[@sujaymessi]

did you find a solution that filters while querying ? I am looking for one.

[@byteseq]

@@sujaymessi add your parameter to the SQL query instead

[@sujaymessi]

@@byteseq i tried that. It keeps giving an error that the parameter is part of the table.

[@byteseq]

@@sujaymessi you're doing something wrong then. I cannot guess what

[@aamirghanchi9487]

Thanks for the video! IS it possible to call a stored procedure with parameters?

[@haim.zara.ivanov.]

Very intrsting. Would you happen to post another video on how to connect parater to a cell ?

[@JyroDante]

kzbin.info/www/bejne/n2Kkf556a7iGeJY

[@NyphronRising]

yeah but what if you want to parameterize the sql query itself. like I have a SQL query SELECT * FROM ORDERS WHERE ORDERDATE=@ORDERDATE. I want to set the ORDERDATE from another cell in Excel. How does one do this?

[@VishaalReddyKuthuru]

I’ve been trying to figure this out too. No luck so far. Been getting error codes like “-C”

[@coloradokid8321]

Typical Microsoft! This used to be an easy thing to do in the older versions of Excel (prior to “Power Query”)!

[@zj10]

What if I have multiple rows of data that I want to search against the sql table? I would I go about this?

[@physicsmostafa1774]

what version of excel are you using?

[@dashboardgear4256]

I am using Office 365

[@brucetedder9166]

Hi Did you do the next video as I cannot find it?

[@jameswyffels9414]

Right? This isnt very helpful without the next piece. Where are you, Dashboard Gear?

[@JustAGuyYaKnow42]

Very helpful. A link to the next video in the series would be helpful. I'm looking how to call a stored procedure using these parameters.

[@drewskavich]

It took some searching, but this is the next week's video: kzbin.info/www/bejne/q3nYZWCmeJKgnLc Unfortunately, he completely abandons what we did this week, and uses a different wizard to get in. I've looked for the following week, but can't find April 23. The next I see is April 30, and he's talking about Cubed Named Sets (based on title -- I haven't watched yet).

[@djdrocco]

@@drewskavich I owe you a drink for saving me 5 minutes of searching :D

[@ashokkumar-eb3dw]

Querying SQL Server Data in Excel with a Parameter can you send me the next link

[@brucetedder9166]

Yeah did you get the next link? Cant find his next video?

[@alvaromdp]

Hello, this is really helpfull. Sorry, where is the next video from this series? I need to connect the Parameter in Power Query to a cell in Excel. Thanks!

[@dashboardgear4256]

Glad you found this helpful! The next session on that topic will be in a few weeks.

[@Maik1976ger]

@@dashboardgear4256 How many weeks? :-) I need the next step ;-)

[@einyv]

Maybe this will help: 1.Excel Tab with the population 2.highlight column with list 3.Data menu choose from table /range 4.box comes Where is your table. mine is in column A with a header so I have in the formula =$A:$A and check my table has headers ok 5.Power query opens up, In my example Loan was my column name. To the left of the name it has a symbol for data type. It might have integers for loans so clock on the symbol to the left and change it to text 5a.. You will want to add column (custom) to pad the loan to ensure usability formal Text.PadStart([Loan],10,"0") --[Loan] is my column name] Then click remove rows in Reduce Rows option. Section to add like remove blanks, remove dupes. this piece saves the steps and will do them all on refresh 6.Name the table on right hand side properties 7.Close and Load. While i do this step i will delete the new tab it is loaded to since it is not needed, i want to make this a connection only. Deleting the tab will make it a connection only query 8.Go to get data and now we will get the query you will use for SQL, get from SQL server 9. Put in the server name, database, advance option to place your query 10. When the preview comes up click transform. 11.choose Merge queries and a merge box will come up. The query you have up already and now choose the population list whatever you called it. 12. click on the column to join the data. You might get a popup about privacy but click ignore since access is at the userid level anyway. Click save _ use the padded column 13. Join Kind, I choose right since the population list in the second. Click ok 14. You might have to expand the loanlist to see every record even when there is not a match (click the box with the left and right arrow in the name in the column name from the right join) 15. You will need to move the population list column which should be at the end to the first position If you are only passing a 1 parameter or a few different parameters with single values you can use step 1-5 to create each parameter list that will hold the single value. You click on manager parameters and add them there. You can also apply cleanup to the parameter if need be to account for formatting. You go to your query and on the column view where it shows your preview sample data click on the column you want to add a parameter, text field then choose the option. If you have a created parameter you can click the button to choose the parameter and save it and load it. You can use instead of sqlserver as your source choose get data from other sources and choose Microsoft query. Initially in the query put in a query with hard coded values to get it set up. once finished you can click on connections and right click the new connection and go to properties. click definitions in the command text where the code is, where ever you need the parameter put ([Fieldname]=? ) Parameters will go in order where they are in the code. ({fieldname is whatever you field is that you want to add the parameter to. once complete click on parameters, There you can add your parameters and where to find them.

[@Kylian19]

The problem i ve is that my data is so big it can't even be loaded to excell..

[@BuckstarrZ]

The title is misleading. You are not affecting the sql query at all, you are only filtering AFTER more data than necessary had been pulled already. I was expecting you showing how to use a parameter as part of the sql query itself.

[@AntiThesis10125]

Filter not parameter

[@Jahspecs1]

This is horrible! Are you really using macros? There are several other KZbin videos that show how todo this without using 1970s macros. Really? Macros?