• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pass a variable onto Power Query

aggie81

Member
Is it possible to use a formula to limit the years of information that a Power Query pulls into a workbook from a SQL database?

something like: year field is >=YEAR(TODAY())-5

So that the query for year 2017 drops off 2011 pulling in only information from years 2012 to 2017, in the year 2018 drops 2012 and so on.

Thanks,
Lee
 
Yes it's possible. There are many ways to do it.

Simplest solution is to use WHERE clause in your SQL statement.

Assuming that there is [YEAR] column in source SQL table.

Code:
Select *
From dbname.dbo.Table1 as t1
Where t1.YEAR >=  Datepart(yyyy, Getdate()) - 5

Alternately you can set up named range and pass it on to PowerQuery by editing Query in Advanced Editor. You can see how single cell named range is used to pass on variable to PowerQuery in thread below.

http://chandoo.org/forum/threads/complex-data-rearrange.30459/#post-182012
 
Thanks, I will try the SQL WHERE and it can be hard coded so the sales people can't change it, it appears to be the easiest for me to do and they can't monkey around with it.
However, I like the thought of being able to pass a custom variable using a named range.
I will read the links both of you reference and appreciate the information.
Lee
 
Back
Top