Pass a variable onto Power Query

[aggie81]

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

 

[NARAYANK991]

Hi ,

Do these links help ?

https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/

http://blogs.adatis.co.uk/callumgreen/post/Query-Parameters-in-Power-Query-Part-1

Narayan

 

[Chihiro]

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.

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

 

[aggie81]

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