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.  This solution works in Excel – I have not attempted to pass a parameter in Power BI yet.

Please note 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.

Add this table into Power Query by selecting the table, then Date > From Table/Range.  When the query editor opens, if necessary, change the data type (in my case it need it to be text).

Right click on the record and select “Drill Down”.  Since you have 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:

We are not going to put a clause in referencing the parameter here (see “Why Not Include the Parameter in the SQL Statement” section below for why).  If you have other criteria for the statement, include that.  Here’s my simple query – note that I’m not including the store field.

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.

When the Query Editor returns the columns (and/or record sampling), for the field you will be using your parameter, filter with any single value (Text Filters > Equals) – this is just a placeholder.

You will now see that filtering in the formula bar.  Replace that 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 it’s 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!

Also, 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.

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 hope this post helped you out.  If so, please 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.

Feel free to share this with someone else who may find it useful. Best of luck to you!

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

Enterprise DNA Learning Summit – November 2019, Ranking Insights Tips

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

Session 3 – Ranking Insights Tips

Using either RANKX or TOPN in Power BI enable some fantastic insights. During this workshop we’ll be running through many variations where you can utilize these DAX functions to enable great analysis. Covered will be the key differences but also the best times to use each of these ranking formula concepts.

• Learn the versatility of ranking formulas with DAX
• Understand the possibilities with ranking functions
• Delve into the nuances of these important functions
• Create unique visualisations with ranking techniques

View on YouTube

Enterprise DNA Learning Summit – November 2019, Dynamic Grouping Concepts

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

Session 2 – Dynamic Grouping Concepts

In this workshop we’ll be covering many ways that you can create dynamic groups in Power BI. This is a powerful technique to broader you visualization potential. Most of the time this type of analysis isn’t immediately possible, but by implementing the right ideas using DAX and in your visuals, you can showcase some compelling insights.

• Learn why you should use this analytical technique
• Discover new ways to create groups for your data
• Uncover valuable insights using these ideas
• Group various dimensions from your data model effectively

View on YouTube

Enterprise DNA Learning Summit – November 2019, Virtual Table Exploration

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

Session 1 – Virtual Table Exploration

During this first session, we will be exploring what virtual tables are in Power BI and how you can use these effectively to discovered advanced insights very fast. This is a very unique topic that will take you Power BI development to the next level if you can understand it well.

• Understand what virtual tables are in Power BI
• Create dynamic data groups within formulas
• Discover advanced insights fast with DAX
• Explore the limitless possibilities with virtual tables

View on YouTube

Upcoming Enterprise DNA November 2019 Learning Summit – What You Will Learn

FOR MORE DETAILS CLICK HERE – https://ift.tt/2q8z6Ni

Details of what will be covered below

Session 1 – Virtual Table Exploration

– Understand what virtual tables are in Power BI
– Create dynamic data groups within formulas
– Discover advanced insights fast with DAX
– Explore the limitless possibilities with virtual tables

Session 2 – Dynamic Grouping Concepts

– Learn why you should use this analytical technique
– Discover new ways to create groups for your data
– Uncover valuable insights using these ideas
– Group various dimensions from your data model effectively

Session 3 – Ranking Insights Tips

– Learn the versatility of ranking formulas with DAX
– Understand the possibilities with ranking functions
– Delve into the nuances of these important functions
– Create unique visualisations with ranking techniques

Session 4 – Developing Enterprise Apps

– 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

Power BI & DAX Master Class – Advanced Analytics w/Power BI

TO VIEW REPLAY & DOWNLOAD RESOURCE REGISTER HERE – https://ift.tt/2oFQZlZ

When is it?
October 16th, 4pm New York (EDT), 1pm Los Angeles (PDT), 9pm London (BST)
October 17th, 7am Sydney (AEDT), 9am Auckland (NZDT)

Where is it? Join in from your laptop, desktop or connected device

Who’s doing it?
Presented by Sam McKay, Founder of Enterprise DNA – Driving an analytical movement through using Power BI!

During This Free Training, You Will Learn:

– Use DAX to create valuables insights
– Learn how to think creatively about what you can analyze
– Bring your analysis and visualization techniques together
– Create compelling reports and dashboard that add value

If you have questions regarding this training, please send it at https://ift.tt/2xDKj94

View on YouTube

Power BI & DAX Master Class – DAX Formula Combinations

TO VIEW REPLAY & DOWNLOAD RESOURCE REGISTER HERE – https://ift.tt/2oFQZlZ

When is it?
October 15th, 4pm New York (EDT), 1pm Los Angeles (PDT), 9pm London (BST)
October 16th, 7am Sydney (AEDT), 9am Auckland (NZDT)

Where is it? Join in from your laptop, desktop or connected device

Who’s doing it?
Presented by Sam McKay, Founder of Enterprise DNA – Driving an analytical movement through using Power BI!

During This Free Training, You Will Learn:

– Learn how to combine many different DAX functions together
– Solve unique analytical scenarios with DAX patterns
– Understand how to use the CALCULATE statement
– Master ‘measure branching’ techniques

If you have questions regarding this training, please send it at https://ift.tt/2xDKj94

View on YouTube

Power BI & DAX Master Class – Fundamentals of DAX

TO VIEW REPLAY & DOWNLOAD RESOURCE REGISTER HERE – https://ift.tt/2oFQZlZ

When is it?
October 14th, 4pm New York (EDT), 1pm Los Angeles (PDT), 9pm London (BST)
October 15th, 7am Sydney (AEDT), 9am Auckland (NZDT)

Where is it? Join in from your laptop, desktop or connected device

Who’s doing it?
Presented by Sam McKay, Founder of Enterprise DNA – Driving an analytical movement through using Power BI!

During This Free Training, You Will Learn:

– Review the concept of context
– Iterating vs Aggregating functions
– Understand how DAX works w/ the data model
– How to structure and organize your measures

If you have questions regarding this training, please send it at https://ift.tt/2xDKj94

View on YouTube

Power BI & DAX Master Class – Data Model Design Deep Dive

TO VIEW REPLAY & DOWNLOAD RESOURCE REGISTER HERE – https://ift.tt/2oFQZlZ

When is it?
October 13th, 4pm New York (EDT), 1pm Los Angeles (PDT), 9pm London (BST)
October 14th, 7am Sydney (AEDT), 9am Auckland (NZDT)

Where is it? Join in from your laptop, desktop or connected device

Who’s doing it?
Presented by Sam McKay, Founder of Enterprise DNA – Driving an analytical movement through using Power BI!

During This Free Training, You Will Learn:

– Learn how to optimize data
– Setup data model structure
– Relationship best practices
– Unique data model designs
– Working w/ multiple tables

If you have questions regarding this training, please send it at https://ift.tt/2xDKj94

View on YouTube

Actuals vs Budgets – Comparison Insights w/Power BI

In this tutorial, I run through how you can compare actuals to budgets in Power BI.

This is an extension of a prior tutorial that covers how to create the ultimate budget calculation with DAX.

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn

FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv

FREE – Power BI Resources – https://ift.tt/2LiAVQj

FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95

Learn more about Enterprise DNA – https://ift.tt/2LiAWDR

Enterprise DNA Membership – https://ift.tt/2IJLxWD

View on YouTube

Calculate Total CRM Forecasts From Today Until End Of Month

In this video tutorial, I showcase how you can work out a dynamic monthly calculation.

This is perfect for a CRM scenario where you may want to dynamically calculate a monthly sales forecast for the remainder of the month.

In this example, we use the DATESBETWEEN function, which enables us to open up a dynamic date window.

Enjoy working through this one.

***** Learning Power BI? *****

FREE COURSE – Ultimate Beginners Guide To Power BI – https://ift.tt/2Lmxubn

FREE COURSE – Ultimate Beginners Guide To DAX – https://ift.tt/2IJLvOv

FREE – Power BI Resources – https://ift.tt/2LiAVQj

FREE – 60 Page DAX Reference Guide Download – https://ift.tt/2IJLx95

Learn more about Enterprise DNA – https://ift.tt/2LiAWDR

Enterprise DNA Membership – https://ift.tt/2IJLxWD

View on YouTube