rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  After much trial and error, I finally have found a solution that works.  Here’s the quick and easy way to do this and harness the power of query folding.  This solution works in Excel – the process is a bit different in Power BI.

Please note that this post assumes you are at least moderately familiar with Power Query and SQL querying. Your mileage may vary depending on your situation.

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • Add a table, with a single record and column.  This can be on any tab in your workbook.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range.  When the query editor opens, if necessary, change the data type (in my case I need it to be text).
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

Create the SQL query:

Build your SQL query as normal, but note that we are not going to put a where clause in referencing the new parameter here (see “Why Not Include the Parameter in the SQL Statement” section below).  Here’s my simple query example – notice that I’m not including the store field in the criteria section of the query though this is the field my parameter will ultimately filter on.

Select * FROM Storelist WHERE OpenDate < GetDate()-365

If you think like me, you may be pausing here – my table has millions of records and I don’t want them all pulling in – don’t worry, this is the beauty of the process.

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!
  • Side note – if you are wondering about GetDate(), this is the SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well.

Why Not Include the Parameter in the SQL Statement?

In the SQL statement, logic would lead us to add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot successfully put the parameter directly into the SQL code/query.  I have tried several times, but the challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).

If you are tempted to test this yourself, the format would be Select * FROM Storelist WHERE Open < GetDate()-365 and Store = “&SParameter&”.  You will likely run into this error “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Please bookmark and subscribe to my blog!  I am always curating and adding new, relevant content! Thanks so much!

Also, be sure to check out Dose for Excel (click image below)!  Add over 100 functions to Excel to increase your productivity and more!  They have a free trial right now, so you can try it out today!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in.  Thanks for supporting my blog!

Dose for Excel - +100 Functions

Convert Date in Access – Serial Number, Text String, First of Month, End of Month, etc.

Convert Date In Access – Serial Number, Text Date, First of Month, End of Month/last day of month.  MS Access will not automatically convert dates into different types, so this post will assist you with doing so quickly. To begin, you will create a new field in your query, and then continue with the conversion below that applies in your scenario. 

Helpful hint: Substitute [yourdate] with the name of the field that you are converting.

To convert a date in the date/time format to the serial number (7/1/2019 to 43647), use:

Cdbl ([yourdate])

———————————–

To convert a date in the serial number format to date/time format (43647 to 7/1/2019), use:

Cdate ([yourdate])

———————————–

To convert a text or string date to the date/time format, try:

DateValue ([yourdate])

———————————–

To convert a date to the first day of the month:

DateSerial (year([yourdate]),month([yourdate]),1)

———————————-

To convert a date to the last day of the month:

DateSerial (year([yourdate]),month([yourdate])+1,1)-1

———————————-

You can also convert a date/timeformat to a string – here is one example:

Format([yourdate],”mmmmyyyy”)

This would render 7/1/19 in your date field as July2019.

More information on the Format function and syntax can be found on the Microsoft Office Support site.

Please feel free to contact me or comment if there are other short tutorials/how-to articles you would like to see. I’d love to help you out.

If I helped you, please consider leaving a tip via PayPal to support the blog! Thanks!!

Subscribe for more great content!

Beautiful: What Fruit Is New Today – 2300

A tough question from the MrExcel Message Board. There are 1500 rows showing products in a delimited string. Someone might have had “Cherry, Apple” yesterday and today they have “Apple, Banana, Cherry”. Can you use a VBA macro to subtract Cherry Apple from Apple Banana Cherry and end up with just Banana?
Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.

View on YouTube

Post to New MrExcel Forum With XL2BB – 2299

We’ve migrated the 1.4 Million posts in the MrExcel Message Board to a new board software. One thing that is different: the steps to post your spreadsheet to the board. This video will take a look at the free XL2BB Excel add-in that you can use.

View on YouTube

Troubleshoot random numbers in Power Query

Creating random numbers in power query is not as straight forward as you might think. In this video we will cover the main issues with this:
1. The random number is the same on each row
2. How to keep the random number from changing

Enjoy!

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

Troubleshoot random numbers in Power Query

Creating random numbers in power query is not as straight forward as you might think. In this video we will cover the main issues with this:
1. The random number is the same on each row
2. How to keep the random number from changing

Enjoy!

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

INDEX & MATCH Excel Lookup Functions – All You Need To Know in 10 Minutes (EMT 1618)

Download Excel File: https://ift.tt/2Ql6XNQ
Learn all about the Excel INDEX & MATCH Lookup Functions is 10 minutes. Here is the list of topics in this video and the time hyperlinks that the specific section of the video:
1. (00:30) Introduction to what INDEX and MATCH can do
2. (01:42) All about MATCH Function
3. (02:04) MATCH for Exact Match Lookup, [match_type] = 0
4. (02:34) What is Approximate Match?
5. (03:16) MATCH for Approximate Match Lookup with values Sorted Ascending, [match_type] = 1 or omitted
6. (04:02) MATCH for Approximate Match Lookup with values Sorted Descending, [match_type] = – 1
7. (04:17) INDEX and MATCH together to do any type of lookup
8. (04:26) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value. Example of Exact Match Lookup.
9. (05:34) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value when lookup values are sorted biggest to smallest. Example of one type of Approximate Match Lookup.
10. (06:17) INDEX & MATCH to do Two-way lookup to retrieve a single value. See second type of Approximate Match Lookup and an Exact Match Lookup.
11. (07:24) How to Copy Two-way lookup formula.
12. (09:01) INDEX & MATCH to lookup a list of values
13. (09:23) INDEX & MATCH to do Two-way lookup to retrieve a full row
14. (10:15) INDEX & MATCH to do Two-way lookup to retrieve a full column
15. (10:41) INDEX & MATCH to do Lookup an item from multiple tables
16. (10:41) MATCH to get Table Number
17. (11:11) INDEX & MATCH to do Lookup Columns Headers
18. (11:56) Summary

View on YouTube

DAX Fridays! #150: The 150th DAX Fridays Challenge, can you solve it?

We are celebrating the 150th DAX Fridays video with a DAX challenge that can be solved with DAX…and no, you dont need to speak Italian to solve it 😉
Post your solutions in the comment box 🙂

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

DAX Fridays! #151: ALL vs ALLEXCEPT

Get Northwind Dataset: https://www.youtube.com/watch?v=k3NMIlLffrU

Link to DAX Fridays survey: http://bit.ly/2MMM4KK

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!Here you can download all the pbix files: https://ift.tt/2yGx9Ih

▲▲▲▲▲▲▲▲▲▲

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#daxfridays #curbal #SUBSCRIBE

View on YouTube

Enterprise DNA Learning Summit – November 2019, Developing Enterprise Apps

EVENT DETAILS – https://ift.tt/2q8z6Ni

Session 4 – Developing Enterprise Apps

The Power BI Online Service has gone through a huge upgrade recently and finally, there is a lot more clarity around how each different feature should be used. During this workshop, we’ll cover how to create compelling apps for your teams and organisations. We’ll also go over many of the new and interesting upgrades that have been created in the web-based platform for Power BI.

• Learn how to build effective app workspaces
• Scale the Power BI online service within your teams
• Manage multiple different reports and insights effectively
• Refresh your enterprise distribution strategies for insights

View on YouTube

Administrate in bulk in Power BI Service

Have you ever wonder how to give access to multiple workspaces at once, or how to restore a deleted workspace? Here is a quick and easy way to do that 🙂

Here you can download all the pbix files: https://ift.tt/2yGx9Ih

SUBSCRIBE to learn more about Power and Excel BI!
https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw?sub_confirmation=1

Our PLAYLISTS:
– Join our DAX Fridays! Series: https://goo.gl/FtUWUX
– Power BI dashboards for beginners: https://goo.gl/9YzyDP
– Power BI Tips & Tricks: https://goo.gl/H6kUbP
– Power Bi and Google Analytics: https://goo.gl/ZNsY8l

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
https://ift.tt/2NEZM2b

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:
Website: http://www.curbal.com
Contact us: https://ift.tt/2qhiZvU

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:

https://ift.tt/2NrgChB

Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲

************

What gear do I use to make my videos and run my business? Below you will find a list of most of my gear. The links on the store are affiliate links, meaning if you buy something from them, amazon will give a small commission and you will be supporting my channel indirectly. Thanks in advance!

https://ift.tt/2I8nl09

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► https://goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► https://goo.gl/bME2sB

#CURBAL #SUBSCRIBE

View on YouTube

High Tech – VLOOKUP First Last name to LAST, FIRST – 2297

Sweeps URL: https://ift.tt/2NRz7gu
Evan has two lists of names. One is Firstname Lastname and the other is LAST, FIRST. He would like to do a VLOOKUP between these. My solution today involves running both lists through Power Query and then doing a Full Outer Join while merging the two lists.

View on YouTube